表
---------
NAME VALUE
AAAA A1
AAAA A2
AAAA A3
BBBB B1
BBBB B2想要的结果
---------
NAME VALUE
AAAA A1,A2,A3
BBBB B1,B2
---------
NAME VALUE
AAAA A1
AAAA A2
AAAA A3
BBBB B1
BBBB B2想要的结果
---------
NAME VALUE
AAAA A1,A2,A3
BBBB B1,B2
WITH tab AS
(SELECT 'AAAA' AS NAME, 'A1' AS VALUE FROM dual UNION
SELECT 'AAAA', 'A2' FROM dual UNION
SELECT 'AAAA', 'A3' FROM dual UNION
SELECT 'BBBB', 'B1' FROM dual UNION
SELECT 'BBBB', 'B2' FROM dual
)
SELECT NAME, SUBSTR(MAX(sys_connect_by_path(VALUE, ',')), 2) AS VALUE
FROM (SELECT NAME, VALUE, LAG(rn) OVER(PARTITION BY NAME ORDER BY rn ) prn , rn
FROM (SELECT tab.name, tab.value, row_number() OVER(PARTITION BY tab.name ORDER BY tab.value ) AS rn
FROM tab
)
)
START WITH prn IS NULL
CONNECT BY PRIOR rn = prn AND PRIOR NAME = NAME
GROUP BY NAME运行结果:
---------------------------------------------------
NAME VALUE
AAAA A1,A2,A3
BBBB B1,B2
明显简单问题被复杂化了,,,,使用:SQL> WITH tab AS
2 (SELECT 'AAAA' AS NAME, 'A1' AS VALUE FROM dual UNION
3 SELECT 'AAAA', 'A2' FROM dual UNION
4 SELECT 'AAAA', 'A3' FROM dual UNION
5 SELECT 'BBBB', 'B1' FROM dual UNION
6 SELECT 'BBBB', 'B2' FROM dual
7 )
8 select NAME, wmsys.wm_concat(VALUE) from tab group by name
9 /NAME WMSYS.WM_CONCAT(VALUE)
---- --------------------------------------------------------------------------------
AAAA A1,A2,A3
BBBB B1,B2SQL>
(SELECT 'AAAA' AS NAME, 'A1' AS VALUE FROM dual UNION
SELECT 'AAAA', 'A2' FROM dual UNION
SELECT 'AAAA', 'A3' FROM dual UNION
SELECT 'BBBB', 'B1' FROM dual UNION
SELECT 'BBBB', 'B2' FROM dual
)
select NAME, wmsys.wm_concat(VALUE) from tab group by name