SQL: Aggregating strings together
In my SQL Server 2005 database, using an SLQ query, does anyone know the best way to group records together by one field, and get a comma-separated list of the values from another?
So if I have:UserID Code
1 A
1 C开发者_C百科5
1 X
2 V3
3 B
3 D
3 NULL
3 F4
4 NULL
I'd get:
UserID Code
1 A,C5,X
2 V3
3 B,D,F4
4 NULL
Thanks for any help.
WITH Data AS (
SELECT 1 UserId, 'A' Code
UNION ALL
SELECT 1, 'C5'
UNION ALL
SELECT 1, 'X'
UNION ALL
SELECT 2, 'V3'
UNION ALL
SELECT 3, 'B'
UNION ALL
SELECT 3, 'D'
UNION ALL
SELECT 3, NULL
UNION ALL
SELECT 3, 'F4'
UNION ALL
SELECT 4, NULL
)
SELECT U.UserId, STUFF((
SELECT ','+Code FROM Data WHERE Data.UserID = U.UserID FOR XML PATH('')
), 1, 1, '') Code
FROM (SELECT DISTINCT UserID FROM Data) U
Just replace the Data
CTE with your table name and you're done.
There it´s a complete review of forms to do that in TSQL
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
精彩评论