方法一:
有如下一张表。
表table id c1 c2
1 1 小明
2 1 老张
3 2 小王
4 2 张三
5 2 李四
i
执行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;
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;
SELECT c1, wm_concat (c2) AS name
FROM table
GROUP BY c1;
结果如下:
c1 c2
1 小明,老张
2 小王,李四,张三
以上方法都有个缺点,连出来的数据顺序是乱的。