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
;
Xiangzhen
