RE: Sql Question

[Aponte, Tony]

Here's a quick-n-dirty SQL that pivots the result set into one row.  It has its limits (you must know the number of rows that would be returned so that you can adjust the grouping columns value01 through value12.    SELECT g1 ,MAX(DECODE(line_no,01,value,NULL)) value01 ,MAX(DECODE(line_no,02,value,NULL)) value02 ,MAX(DECODE(line_no,03,value,NULL)) value03 ,MAX(DECODE(line_no,04,value,NULL)) value04 ,MAX(DECODE(line_no,05,value,NULL)) value05 ,MAX(DECODE(line_no,06,value,NULL)) value06 ,MAX(DECODE(line_no,07,value,NULL)) value07 ,MAX(DECODE(line_no,08,value,NULL)) value08 ,MAX(DECODE(line_no,09,value,NULL)) value09 ,MAX(DECODE(line_no,10,value,NULL)) value10 ,MAX(DECODE(line_no,11,value,NULL)) value11 ,MAX(DECODE(line_no,12,value,NULL)) value12 FROM (SELECT g1,value,row_number() over(partition by g1 order by g1 nulls last) line_no FROM (SELECT 'DEPTNO' g1,deptno value from dept) ) GROUP BY g1;     I took it a little further and came up with the following:   SELECT g1 ,MAX(DECODE(line_no,01,value,NULL)) ||','|| MAX(DECODE(line_no,02,value,NULL)) ||','|| MAX(DECODE(line_no,03,value,NULL)) ||','|| MAX(DECODE(line_no,04,value,NULL)) ||','|| MAX(DECODE(line_no,05,value,NULL)) ||','|| MAX(DECODE(line_no,06,value,NULL)) ||','|| MAX(DECODE(line_no,07,value,NULL)) ||','|| MAX(DECODE(line_no,08,value,NULL)) ||','|| MAX(DECODE(line_no,09,value,NULL)) ||','|| MAX(DECODE(line_no,10,value,NULL)) ||','|| MAX(DECODE(line_no,11,value,NULL)) ||','|| MAX(DECODE(line_no,12,value,NULL)) FROM (SELECT g1,value,row_number() over(partition by g1 order by g1 nulls last) line_no FROM (SELECT 'DEPTNO' g1,deptno value from dept) ) GROUP BY g1;   I'll leave the trimming of the trailing commas to you.   HTH Tony Aponte   -----Original Message----- From: kranti pushkarna [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 30, 2002 3:48 AM To: Multiple recipients of list ORACLE-L Subject: Sql Question Hi List,             Can someone give a SQL query to retuen all values in paricular column in comma separed format. e.g. suppose I fire "select deptno from dept" the output would be like   Deptno 10 20 30 40   I want the output like 10,20,30,40.   I am just wondering can it be done in a single query.     TIA Kranti