SQL combining data from two rows into a single row when they share a common identifier

Question

For a report I'm working on I need to combine data from rows in a specific column based on the data they share in a separate column. All data is from the same table:

      ID       |        Name        | 
    10000             Jane Doe            
    10000             John Smith
    50000             Jack Bauer
    50000             Jon Snow
    90000             Jack Bauer

I need to somehow combine the Name row together when they share a common ID value:

      ID       |        Name        | 
    10000             Jane Doe, John Smith            
    50000             Jack Bauer, Jon Snow
    90000             Jack Bauer

The ID is not the primary key for this table but it's what I need as the unique value on the report.

I'm mostly self taught and admittedly lacking a perfect SQL vocabulary. I can't quite think of how to articulate the code in order to do this. Any help with the logic would be immensely appreciated!

Dave

Does this table have a PRIMARY KEY? Also, which RDBMS? Also, see GROUP_CONCAT — Strawberry, May 07 '14 at 18:27
Are you using SQL Server or MySQL? These are different databases and in general a question should be tagged with only the one you are using. — Gordon Linoff, May 07 '14 at 18:32
FOR XML would do. Lot of questions about that on Stackoverflow. Will give you a link if I find one — codingbiz, May 07 '14 at 18:42
possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — podiluska, May 07 '14 at 18:44
@dave814 re your recent question, check out http://stackoverflow.com/questions/11432852/how-to-add-a-new-sheet-in-excel-using-vba — Fionnuala, May 14 '14 at 15:51
@Remou thank you very much! Exactly what I was looking for. I guess I'm still getting used to the culture here — dave814, May 14 '14 at 15:56

score 5 · Accepted Answer · answered May 07 '14 at 18:55

Test Data

DECLARE @TABLE TABLE (ID INT, Name VARCHAR(50))
INSERT INTO @TABLE VALUES
(10000,'Jane Doe'),            
(10000,'John Smith'),
(50000,'Jack Bauer'),
(50000,'Jon Snow'),
(90000,'Jack Bauer')

Query

SELECT t.ID
      ,STUFF((SELECT ', ' + Name
              FROM @Table
              WHERE ID = t.ID 
              FOR XML PATH(''),TYPE)
              .value('.','NVARCHAR(MAX)'),1,2,'') AS Names
FROM @TABLE t
GROUP BY t.ID

Result Set

╔═══════╦══════════════════════╗
║  ID   ║        Names         ║
╠═══════╬══════════════════════╣
║ 10000 ║ Jane Doe, John Smith ║
║ 50000 ║ Jack Bauer, Jon Snow ║
║ 90000 ║ Jack Bauer           ║
╚═══════╩══════════════════════╝

SQL combining data from two rows into a single row when they share a common identifier

1 Answers1

Linked