T-SQL Concatenate & group multiple rows into a single row

Question

I'm looking for a way to group whilst also concatenating rows into a comma separated string. Example:

Name            Place
Steve Jones     New York
Steve Jones     Washington
Albert Smith    Miami
Albert Smith    Denver

to ...

Steve Jones    New York, Washington
Albert Smith   Miami, Denver

Greatly appreciated

This is generally MUCH easier to do at the display layer (web page, report, app, whatever) than in SQL. It's _possible_ but TSQL does not have a clean syntax to do string aggregation. — D Stanley, Aug 15 '17 at 16:32

score 7 · Answer 1 · answered Aug 15 '17 at 16:30

If you use SQL Server 2008 and above, you can use STUFF and XML PATH to get the result you want.

SELECT DISTINCT Name
    , STUFF((
        SELECT ',' + Place
        FROM YourTable t1
        where t1.Name = t2.Name
        FOR XML PATH('')
    ), 1, 1, '') AS Places
FROM YourTable t2

score 1 · Answer 2 · answered Aug 15 '17 at 16:40

Using CROSS APPLY and FOR XML PATH:

SELECT distinct PP.Name, SUBSTRING(A.Places, 0, LEN(A.Places)) AS CombinedPlaces
FROM PersonPlace PP
CROSS APPLY 
(
    SELECT place + ', ' 
    FROM PersonPlace AS AP
    WHERE AP.Name = PP.Name
    FOR XML PATH('')        
) A(places)

score 0 · Answer 3 · answered Aug 15 '17 at 15:42

0

You can use String_Agg if you are using 2017, SQL Azure else you can query as below:

Select [Name],
    Places = Stuff((Select ', '+Place from yourtable where [Name] = t.[Name] for xml path('')),1,2,'')
    from yourtable
    group by [Name]

answered Aug 15 '17 at 15:42

Kannan Kandasamy

13,405
3
25
38

score 0 · Answer 4 · answered Aug 15 '17 at 16:26

There's probably a simpler way, but this works:

with N as
(
    select name, count(*) over (partition by name order by name) c from PersonPlace
),

Q as
(
    select J.name, J.place, cast(J.place as varchar) place_name, J.c cnt, 1 c from
        (select distinct(name) from PersonPlace) K
            cross apply
            (
                select top 1 P.*, N.c from PersonPlace P
                    inner join N on N.name = P.name

                where P.name = K.name
            ) J

    union all
        select P.name, P.place, cast(Q.place + ', ' + P.place as varchar), Q.cnt, Q.c + 1  

             from PersonPlace P 
                inner join Q on Q.name = P.name and Q.c + 1 <= cnt and P.place <> Q.place

)

select Q.name, Q.place_name from Q where Q.c  = Q.cnt

Results:

name            place_name
--------------  ----------------------
Steve Jones     New York, Washington  
Albert Smith    Denver, Miami

Rextest Demo

If the People and Places are actually separate tables with their own keys, then we can simply quite a bit.

T-SQL Concatenate & group multiple rows into a single row

4 Answers4

Linked