I have input as
12 abc
12 def
34 ghi
i want output as
12 abc,def
34 ghi
How to achieve this using sql.Thanks!
Add a comment
|
2 Answers
I think, the question is not about converting rows to columns (PIVOT) but about aggregating strings. If you are on 11.2 you can use LISTAGG:
with q as (select '12' id, 'abc' col from dual
union all
select '12' id, 'def' col from dual
union all
select '34' id, 'ghi' col from dual )
select id, listagg(col,',') within group (order by col) col_agg
from q;
You can find another alternatives here: [http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php]
Comments
If you are using oracle 11g
create table test(id int,val char(3));
insert into test values(12,'abc');
insert into test values(12,'def');
insert into test values(34,'ghi');
Query
SELECT id, LISTAGG(val, ',') WITHIN GROUP (ORDER BY val) AS values
FROM test
GROUP BY id;
1 Comment
Maheswaran Ravisankar
Great, but I am afraid the other answer has the same!