2013年3月19日 星期二

Oracle SQL SYS_CONNECT_BY_PATH 合併多行字符串

select  temp.c1, substr(max(SYS_CONNECT_BY_PATH(c2,',')),2) as c_list
from (select c2, c1, ROW_NUMBER() OVER (PARTITION BY c2 ORDER BY c1) as rn
from table1
where c1 = 'A123456789'
order by c1) temp
START WITH temp.rn = 1
CONNECT BY temp.c1 = PRIOR temp.c1
and temp.rn -1 = PRIOR temp.rn
GROUP BY temp.c1
order by temp.c1;


參考:http://hsunya.wordpress.com/2010/10/13/oracle-sys_connect_by_path/