sys_connect_by_path的使用例子

SELECT id event_id,
cnt ggggance_reason_cnt,
LTRIM(SYS_CONNECT_BY_PATH(display_name, '; '),'; ') gggance_reason_list
FROM (
--within each id, assign a row number and get a total count
SELECT id,
display_name,
ROW_NUMBER() OVER (
PARTITION BY id
ORDER BY seq) num,
COUNT(*) OVER (
PARTITION BY id) cnt
FROM (
--Get distinct id and display name
SELECT er.event_id id,
r.reason_name display_name,
r.sequence_num seq
FROM xxxar_reason er,
xxxason r
WHERE r.reason_id = er.reason_id
) sub1
) sub2
WHERE num = cnt
START WITH num = 1
CONNECT BY PRIOR num = num-1
AND PRIOR id = id