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, memberid, string, val)
VALUES('a', 9, 'stra2', 7);
INSERTINTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 2, 'strb1', 3);
INSERTINTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 4, 'strb2', 7);
INSERTINTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 5, 'strb3', 3);
INSERTINTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 9, 'strb4', 11);
INSERTINTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 3, 'strc1', 8);
INSERTINTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 7, 'strc2', 10);
INSERTINTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 9, '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,strb2,strb3,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, 1, '') AS string
FROM dbo.Groups AS G1
GROUPBY groupid;
GO

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

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

转载于:https://www.cnblogs.com/Joe-T/archive/2011/08/11/2134715.html