表中某一列拼接 大神请进~~~~~~~~
数据库:oracle10g
我有一张表如:
id uuid name
1 11 张三
2 11 里斯
3 22 王五
4 22 小六
想要的结果就是讲uuid相等的name拼接 期望结果如下:
id uuid name
1 11 张三,里斯
2 22 王五,小六
就大神们赐教---------------希望大神们先本地测试、谢谢!!!
------解决方案--------------------select id=row_number()over(partition by 1 order by getdate()),uuid,stuff((select ','+name from tb tb2 where tb2.uuid=tb1.uuid for xml path('')),1,1,'')name from tb tb1 group by uuid
------解决方案--------------------
with t1 as
(
select '11'c2,'张三' c3 from dual
union all
select '11','李四' from dual
union all
select '11','王五' from dual
union all
select '22','候六' from dual
union all
select '22','黄七' from dual
)
select c2,wm_concat(c3) c3
from t1
group by c2
c2 c3
---------------------------------------
1 11 张三,李四,王五
2 22 候六,黄七
------解决方案--------------------with t1 as创建临时表 相当于你的表 直接运行后面的sql就行了
select uuid,wm_concat(name) "name"
from t1
group by uuid
------解决方案--------------------select c2,wm_concat(diatinct c3) c3
from t1
group by c2
这样 默认的是 , 如果要替换符号
可以 用
select c2,replac(wm_concat(diatinct c3),',','-') c3
from t1
group by c2
------解决方案--------------------如果你的数据库是10g或以上时,可以用WMSYS.WM_CONCAT这个包
WITH T1 AS
(SELECT '11' C2, '张三' C3
FROM DUAL
UNION ALL
SELECT '11', '李四'
FROM DUAL
UNION ALL
SELECT '11', '王五'
FROM DUAL
UNION ALL
SELE