Group row values into a comma-delimited list

Question

Possible Duplicate:
SQL Query to get aggregated result in comma seperators along with group by column in SQL Server

I have two following tables:

CarID | CarName  | Color  
------+----------+----------
1     | Ford     | White  
2     | Mitsu    | Black  
3     | Mitsu    | Green  

OwnerID | OwnerName | CarID  
--------+-----------+-------
1       | John      | 1  
1       | John      | 3  
2       | Mark      | 2

And I need a following report:

CarName | Owners  
--------+------------
Ford    | John  
Mitsu   | John, Mark

How could it be done?

Are you asking how to do this in TSQL - which has been asked many times before on this site - or using Reporting Services? — Pondlife, Jan 18 '13 at 16:30

score 4 · Answer 1 · answered Jan 18 '13 at 11:17

It's easiest to construct the correct dataset this at the database level, something like:

SELECT
    c.CarName,
    STUFF
    (
        (
            SELECT ',' + owner
            FROM Owner o
            WHERE c.carID = o.carID
            ORDER BY owner
            FOR XML PATH('')
        ), 1, 1, ''
    ) AS owners
FROM Car c

This will return one row for each car/color combination, so one more thing to consider is whether you want to ignore color - then you'll need to group on car name:

SELECT
    c.CarName,
    STUFF
    (
        (
            SELECT ',' + owner
            FROM (select cd.carname, o.owner from Owner o inner join Car cd on o.carID = cd.carID) o
            WHERE c.CarName = o.CarName
            ORDER BY owner
            FOR XML PATH('')
        ), 1, 1, ''
    ) AS owners
FROM (select distinct carname from Car) c

Once you've got the correct dataset it's just a matter of creating a simple table to display the results.

Group row values into a comma-delimited list

1 Answers1