select col,wmsys.wm_concat(col2) from table group by col
with temp as( select '张三' name,'渝北' address,'BSC1' recode from dual union all select '张三' name,'渝北' address,'BSC2' recode from dual union all select '张三' name,'渝北' address,'BSC3' recode from dual ) select name,address,wm_concat(recode) from temp group by name,address;
10G以上用以上的方法,10G以下的用CONNECT BY 实现
--这个使用sys_connect_by_path比较通用 WITH temp AS (SELECT '张三' NAME, '渝北' address, 'BSC1' recode FROM dual UNION ALL SELECT '张三' NAME, '渝北' address, 'BSC2' recode FROM dual UNION ALL SELECT '张三' NAME, '渝北' address, 'BSC3' recode FROM dual union all SELECT '李四' NAME, '渝北' address, 'BSC1' recode FROM dual UNION ALL SELECT '李四' NAME, '渝北' address, 'BSC2' recode FROM dual)
SELECT a.name, a.address, ltrim(MAX(sys_connect_by_path(recode, ',')), ',') recode FROM (SELECT row_number() over(PARTITION BY t.name, t.address ORDER BY recode) rn, t.* FROM temp t) a START WITH rn = 1 CONNECT BY PRIOR rn = rn - 1 GROUP BY a.name, a.address
with temp as(
select '张三' name,'渝北' address,'BSC1' recode from dual
union all
select '张三' name,'渝北' address,'BSC2' recode from dual
union all
select '张三' name,'渝北' address,'BSC3' recode from dual
)
select name,address,wm_concat(recode) from temp group by name,address;
WITH temp AS
(SELECT '张三' NAME, '渝北' address, 'BSC1' recode FROM dual
UNION ALL
SELECT '张三' NAME, '渝北' address, 'BSC2' recode FROM dual
UNION ALL
SELECT '张三' NAME, '渝北' address, 'BSC3' recode FROM dual
union all
SELECT '李四' NAME, '渝北' address, 'BSC1' recode FROM dual
UNION ALL
SELECT '李四' NAME, '渝北' address, 'BSC2' recode FROM dual)
SELECT a.name, a.address, ltrim(MAX(sys_connect_by_path(recode, ',')), ',') recode
FROM (SELECT row_number() over(PARTITION BY t.name, t.address ORDER BY recode) rn, t.* FROM temp t) a
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1
GROUP BY a.name, a.address