SQL: Aggregating strings together

Question

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            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

Thanks for any help.

possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Dar, Aug 06 '15 at 08:13

Lucero · Accepted Answer · 2011-03-27T23:09:15.530

17

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.

edited Mar 27 '11 at 23:09

answered Mar 27 '11 at 21:59

Lucero

57,903
8
117
151

1

-1 for not keeping with output (this would fail an ACM programming contest question). Output has ',' not ', ' as delimiter – RichardTheKiwi Mar 27 '11 at 22:55
1

@Richard, I hope you got some satisfaction for that... Anyways, I fixed it, thanks for the hint. – Lucero Mar 27 '11 at 23:11
2

I thought the -1 would get your attention :) Didn't actually follow through with a vote. +1 – RichardTheKiwi Mar 27 '11 at 23:21
@Richard, I see, thanks... I usually try to follow up on comments, regardless of votes ;) – Lucero Mar 28 '11 at 11:01

score 2 · Answer 2 · answered Mar 27 '11 at 22:07

2

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/

answered Mar 27 '11 at 22:07

pcofre

3,896
16
27

SQL: Aggregating strings together

2 Answers2

Linked

Related