Inside SQLServer 2005 读书随笔-用For XML Path来聚合字符串类

USE?tempdb;
GO
IFOBJECT_ID('dbo.Groups')?ISNOTNULL
??DROPTABLE?dbo.Groups;
GO

CREATETABLE?dbo.Groups
(
??groupid??VARCHAR(10)?NOTNULL,??memberid?INTNOTNULL,??string???VARCHAR(10)?NOTNULL,??val??????INTNOTNULL,??PRIMARYKEY?(groupid,?memberid)
);
????
INSERTINTO?dbo.Groups(groupid,?memberid,?string,?val)
??VALUES('a',?3,?'stra1',?6);
INSERTINTO?dbo.Groups(groupid,?9,?'stra2',?7);
INSERTINTO?dbo.Groups(groupid,?val)
??VALUES('b',?2,?'strb1',?3);
INSERTINTO?dbo.Groups(groupid,?4,?'strb2',?5,?'strb3',?'strb4',?11);
INSERTINTO?dbo.Groups(groupid,?val)
??VALUES('c',?'strc1',?8);
INSERTINTO?dbo.Groups(groupid,?7,?'strc2',?10);
INSERTINTO?dbo.Groups(groupid,?'strc3',?12);
GO

表中数据内容：

?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来分组聚合string字段成类似于数组的形式："stra1,stra2","strb1,strb2,strb3,strb4".....

传统的方法，不过貌似有点难想到：

Select?groupid,Max(Casewhen?rn=1then?string?else''End)
???????????????+Max(Casewhen?rn=2then','+string?else''End)
???????????????+Max(Casewhen?rn=3then','+string?else''End)
????????????????+Max(Casewhen?rn=4then','+string?else''End)
????????????????AS?strs
From?
(Select?Groupid,string,ROW_NUMBER()?over(partition?by?groupid?orderby?memberid)?as?rn
From?dbo.Groups)?AS?T
groupby?groupid
go

groupid??? strs
---------- -------------------------------------------
a????????? stra1,stra2
b????????? strb1,strb4
c????????? strc1,strc2,strc3

还有一中在后来被作者称为专用解决方案和完美解决案的查询：

SELECT?groupid,??STUFF((SELECT','+?string?
?????????FROM?dbo.Groups?AS?G2
?????????WHERE?G2.groupid?=?G1.groupid
?????????ORDERBY?memberid
?????????FOR?XML?PATH('')),?1,?'')?AS?string
FROM?dbo.Groups?AS?G1
GROUPBY?groupid;
GO

MSDN关于FOR XML PATH('')示例说法是：

由此可以得一个完美的查询方案。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!