Now to go the other way…
Prior to Oracle 11gR2, this required a number of creative solutions that can be found on AskTom.oracle.com.
Now you can use a built-in listagg aggreation utility.
e.g. listagg (ename, ‘,’)
where you can pass in the field you wish to build your list with and an additional parameter input to define the delimiter to use.
select
deptno,
listagg (ename, ',') WITHIN GROUP (ORDER BY ename)
enames
from
emp
group by
deptno;
DEPTNO ENAMES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
select
deptno,
listagg (ename, '|') WITHIN GROUP (ORDER BY ename)
enames
from
emp
group by
deptno;
DEPTNO ENAMES
---------- --------------------------------------------------
10 CLARK|KING|MILLER
20 ADAMS|FORD|JONES|SCOTT|SMITH
30 ALLEN|BLAKE|JAMES|MARTIN|TURNER|WARD
P.S. Don’t have 11gR2 ? Here are some other options for you :
http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm
http://www.dba-oracle.com/t_converting_rows_columns.htm


