今天在看订阅的RSS的时候,看到这么一个问题:T-Sql中如何对分组的信息进行聚合,并以逗号连接字符;也就是对一个表中的某个字段进行分组,然后对另一个字段聚合,如果表达得不太清楚,请看下面的表。
原表:
Parent | Child |
Charles | William |
Charles | Harry |
Anne | Peter |
Anne | Zara |
Andrew | Beatrice |
Andrew | Eugenie |
处理后的结果:
Parent | Children |
Charles | William,Harry |
Anne | Peter,Zara |
Andrew | Eugenie,Beatrice |
貌似很简单,以我的思考,先写一个聚合函数,然后再查询语句里面调用这个聚合函数;实际上还有更简单的办法,这是作者给出的解决办法,没有用到自定义聚合函数,他用的是FOR XML PATH(‘’)这样的处理方式,感觉真是爽
with
t
as
(
select ' Charles ' parent, ' William ' child union
select ' Charles ' , ' Harry ' union
select ' Anne ' , ' Peter ' union
select ' Anne ' , ' Zara ' union
select ' Andrew ' , ' Beatrice ' union
select ' Andrew ' , ' Eugenie '
)
SELECT parent, STUFF ( ( SELECT ' , ' + child
FROM t a
WHERE b.parent = a.parent
FOR XML PATH( '' )), 1 , 1 , '' ) children
FROM t b
GROUP BY parent
select ' Charles ' parent, ' William ' child union
select ' Charles ' , ' Harry ' union
select ' Anne ' , ' Peter ' union
select ' Anne ' , ' Zara ' union
select ' Andrew ' , ' Beatrice ' union
select ' Andrew ' , ' Eugenie '
)
SELECT parent, STUFF ( ( SELECT ' , ' + child
FROM t a
WHERE b.parent = a.parent
FOR XML PATH( '' )), 1 , 1 , '' ) children
FROM t b
GROUP BY parent
如果你还有其他的解决办法,希望你也能给出你的答案, 多多益善
2014-5-5 update:
另外,有同学问,怎么逆向还原回去呢?很简单:
CREATE FUNCTION dbo.SplitStrings
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
AS
RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number,
CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
WHERE Number <= CONVERT(INT, LEN(@List))
AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
) AS y);
GO
;with t1 as(
select 'Charles' parent, 'Harry,William' children union
select 'Andrew' , 'Beatrice,Eugenie' union
select 'Anne', 'Peter,Zara'
)
SELECT parent,f.Item AS child FROM t1
CROSS APPLY dbo.SplitStrings(children,',') f