字段拼接二：使用转置

前段时间写了个帖子，主题为使用FOR XML PATH实现字段拼接，传送门如下：http://blog.csdn.net/weixin_38357227/article/details/78460857

最近看书学习时，发现另一种实现字段拼接的方法，即利用转置。
表结构如下：

declare @s table(groupid varchar(10) not null,memberid int not null,string varchar(10) not null,val int not null,primary key(groupid,memberid))insert into @s(groupid ,memberid,string,val) values('a',3,'stra1',6),('a',9,'stra2',7),('b',2,'strb1',3),('b',4,'strb2',7),('b',5,'strb3',3),('b',9,'strb4',11),('c',3,'strc1',8),('c',7,'strc2',10),('c',9,'strc3',12)

groupid memberid string val a 3 stra1 6 a 9 stra2 7 b 2 strb1 3 b 4 strb2 7 b 5 strb3 3 b 9 strb4 11 c 3 strc1 8 c 7 strc2 10 c 9 strc3 12

groupid memberid string val
a 3 stra1 6
a 9 stra2 7
b 2 strb1 3
b 4 strb2 7
b 5 strb3 3
b 9 strb4 11
c 3 strc1 8
c 7 strc2 10
c 9 strc3 12
首先要对参与聚合计算的值进行旋转，当他们全部位于同一列时，就可以在各列之间进行线性运算。注意，除非组中有一列包含序列值，否则需要先计算出行号，以标识元素在组中的位置。

select * from (select groupid,string,row_number() over(partition by groupid order by memberid ) as rnfrom @s) as d pivot(max(string) for rn in  ([1],[2],[3],[4])) as p 

结果如下：

groupid 1 2 3 4 a stra1 stra2 NULL NULL b strb1 strb2 strb3 strb4 c strc1 strc2 strc3 NULL

进行字符串连接：

select groupid,[1]+coalesce(','+[2],'')+coalesce(','+[3],'') +coalesce(','+[4],'')  as string from (select groupid,string,row_number() over(partition by groupid order by memberid ) as rnfrom @s) as d pivot(max(string) for rn in  ([1],[2],[3],[4])) as p 

groupid string a stra1,stra2 b strb1,strb2,strb3,strb4 c strc1,strc2,strc3