CODEWITHSUNDEEP
sqlsql-server-2005
Luke
Luke

Reputation: 1258

SQL: Aggregating strings together

What is 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            C5
  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

I'm using SQL Server 2005 database. Thanks for any help.

Upvotes: 16

Views: 8243

Answers (2)

Lucero
Lucero

Reputation: 60276

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.

Upvotes: 20

pcofre
pcofre

Reputation: 4076

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/

Upvotes: 2

Related Questions