Get Columns Values in Row in SQL Query

By | December 8, 2015

Requirement:

We have two columns in a table. One column having test name and other column having defect names against one test. We need to write a query to bring all defects against the test.

Creating Scenario & its Solutions

Tables Creation Script

create table testcase(testname varchar2(100),defname varchar2(100))

Function to get the list in a row

create or replace function get_def_list(p_testname varchar2) return varchar2
is
cnt number:=0;
defname_str varchar2(500);
cursor c1 is
select testname,defname from testcase where testname = p_testname;
begin

for r in c1 loop
–dbms_output.put_line(‘testname’||r.testname||’defname’||r.defname);
if cnt =0 then
defname_str := r.defname;
cnt :=1;
else
defname_str := defname_str||’, ‘ ||r.defname;
end if;

end loop;

return defname_str;
end;
/

Create Data in table

Insert into TESTCASE
(TESTNAME, DEFNAME)
Values
(‘test1’, ‘def 1’);
Insert into TESTCASE
(TESTNAME, DEFNAME)
Values
(‘test1’, ‘def 2’);
Insert into TESTCASE
(TESTNAME, DEFNAME)
Values
(‘test1’, ‘def 3’);
COMMIT;

Apps Technical

Query to Get the Result

select distinct testname,get_def_list(testname) defectnames_list from testcase

Result Looks like following

Columns in Row

Related Posts

Get Free Online HRMS Skills Assessment
Useful Apps SQL Queries Pool
How to create backend logging mechanism
Step to Register Discoverer Report
Steps for creating custom form in Oracle Apps
Clear Global Cache in Oracle Apps
Get Profile Values in Oracle Forms Personalization
Enable Diagnostic Examine in Oracle Apps
2 simple Steps to make a responsibility read only
How to Enable OAF Personalization