继上篇《SQL 列转行 合并多条记录》后,有网友反馈新的需求还是不太会用。

现举例说明

一,网友需要如下的效果:

SQL 列转行 分组去重并合并多条记录-LMLPHP

其实,这个需求依然可以我上篇的方法进行解答,但为了实现分组,需要distinct group1,同时,为了根据key1,key2,key3是否相同进行分组,所以要用where进行连接。

语法格式:select .....from t1 where key1=a.key1 and key2=a.key2... for xml path('')

二,进入正题,代码如下:

 select distinct group1,key1,key2,key3,
(select concat1+',' from concat where group1=a.group1 and key1=a.key1 and key2=a.key2 and key3=a.key3 for XML PATH('') ) as concat1,
(select concat2+',' from concat where group1=a.group1 and key1=a.key1 and key2=a.key2 and key3=a.key3 for XML PATH('') ) as concat2,
(select concat3+',' from concat where group1=a.group1 and key1=a.key1 and key2=a.key2 and key3=a.key3 for XML PATH('') ) as concat3
from concat a

三、效果如下:

SQL 列转行 分组去重并合并多条记录-LMLPHP

说得不好,欢迎大家拍砖。

04-15 11:53
查看更多