开发者

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/

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜