参考资料:
http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php
项目中需要把某张表分组后,某列合并显示,当前做法是使用 wm_concat,缺点是不能指定分隔符,只能用“,”接着带来的问题是在导出csv格式的文件时,因为逗号,使该字段内容自动分列。接着找资料尝试了下面的方法:
SELECT A,
LTRIM(MAX(SYS_CONNECT_BY_PATH(B,';'))KEEP (DENSE_RANK LAST ORDER BY curr),';') AS B
FROM (SELECT A,
B,
ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) AS curr,
ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) -1 AS prev
FROM table where C = '120911')
GROUP BY B
CONNECT BY prev = PRIOR curr AND A = PRIOR A
START WITH curr = 1;
但项目涉及的表和要合并的字段有多个,改动较大,最后还是在写文件的时候,用分号替换一下逗号,当然,效率会比之前差点。