sql rows to column conversion.converting the data from rows to columns

Question

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!

Answer 1

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]

Answer 2

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;

SQL Fiddle Demo