[重发]分组连接列字符串方法
又翻了一把，顺便转自己文章一篇过来。原文发表于2007年6月20日
共享一个Oracle技巧吧。在以前一篇blog里我写过自定义聚集函数——一条语句将多行的列中字符串连接起来，技术总是在进步的。在这一期的Oracle Magazine中的AskTom，学到了一个不错的方法。从8i开始Oracle增加了分析函数，从9iR1开始，又有了SYS_CONNECT_BY_PATH函数，于是，产生了下面一个更加简单的方法。
CREATE TABLE test AS
SELECT rownum office, chr(ascii(‘a’) + rownum – 1) staff
FROM all_objects
WHERE rownum < 10;
INSERT INTO test
SELECT rownum, chr(ascii(‘o’) + rownum – 1)
FROM all_objects
WHERE rownum < 10;
SELECT * FROM test;
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
1 o
2 p
3 q
4 r
5 s
6 t
7 u
8 v
9 w
SELECT office, ltrim(MAX(sys_connect_by_path(staff, ‘,’)), ‘,’) namelist
FROM (SELECT office,
staff,
row_number() over(PARTITION BY office ORDER BY staff) rn
FROM test) v
START WITH rn = 1
CONNECT BY PRIOR rn = rn – 1
AND PRIOR office = office
GROUP BY office
ORDER BY office;
1 a,o
2 b,p
3 c,q
4 d,r
5 e,s
6 f,t
7 g,u
8 h,v
9 i,w
其实原理并不复杂，就是通过ROW_NUMBER()函数实现分组编号，然后根据编号递增来分组连接，将SYS_CONNECT_BY_PATH函数第二个参数以及外面的LTRIM参数替换成你需要的符号就可以了。不过注意两点哦，一个，这个方法只有在Oracle9iR1之后才能用；第二，如果你想用空（”）连接，那不能直接在SYS_CONNECT_BY_PATH里用空（”）作为参数，而要用其他字符然后在外面用REPLACE替换，否则就可能遇到ORA-30003错误，即SYS_CONNECT_BY_PATH的参数非法。
有了这个法子，原来那个复杂的方法基本可以抛弃咯^_^
