What do you do if someone has more than 4 children -Just unpivot it and sort them back.
drop table t purge;
create table t(x int,c1 varchar2(2),
c2 varchar2(2), c3 varchar2(2),c4 varchar2(2),
c5 varchar2(3), c6 varchar2(7));
insert into t values(1,'a','c','b','e',null,'d');
insert into t values(2,'aa','c','be','ee',null,'ad');
commit;
rajesh@ORA11G> select x,
2 listagg(val,',') within group(order by val) y
3 from ( select c1,c2,c3,c4,c5,c6,x from t )
4 unpivot exclude nulls
5 ( val for key in (c1,c2,c3,c4,c5,c6) )
6 group by x ;
X Y
---------- --------------------
1 a,b,c,d,e
2 aa,ad,be,c,ee
2 rows selected.