The best way to convert multiple rows to one column.

Hi Guys,

--the follow is the best way to convert multiple rows to one column.

select c1, MAX(SUBSTR(SYS_CONNECT_BY_PATH(c3, '|'), 2))

  from (SELECT c1,

               c3,

               c1 || ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY c2) AS sn,

               c1 ||(ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY c2)-1) AS sn2

          FROM (select b.run_id c1, a.name c2,b.answer_text c3

                  FROM siebel.s_cs_quest a JOIN siebel.s_cs_run_answr b ON a.ROW_ID = b.QUEST_ID

                 --and a.last_upd>date '2007-12-1'

                 where b.run_id in ('1-1AR0NCH','1-UMN9','1-11P8Q')

                 ORDER BY b.run_id, a.NAME

                 )

                 )

start with sn = c1 || 1

connect by prior sn = sn2

group by c1

;

Thanks
Xiangzhen