oracle 把一张表中的某字段的多条记录用逗号连接,特别适用于记录个数不固定的表。
方法一:
有如下一张表。
id | c1 | c2 |
---|---|---|
1 | 1 | 小明 |
2 | 1 | 老张 |
3 | 2 | 小王 |
4 | 2 | 张三 |
5 | 2 | 李四 |
执行sql语句如下:
SELECT c1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (c2, ',')), 2) NAME
FROM (SELECT c1, c2, rn, LEAD (rn) OVER (PARTITION BY c1 ORDER BY rn) rn1
FROM (SELECT c1, c2, ROW_NUMBER () OVER (ORDER BY c2) rn
FROM table))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY c1;
结果如下:
c1 | c2 |
1 | 小明,老张 |
2 | 小王,张三,李四 |
方法二:适用于oracle 10g
SELECT c1, wm_concat (c2) AS name
FROM table
GROUP BY c1;
结果如下:
c1 | c2 |
1 | 小明,老张 |
2 | 小王,李四,张三 |
以上方法都有个缺点,连出来的数据顺序是乱的。
参考资料:http://blog.csdn.net/winternight/article/details/6889284