Getting a list of columns to show next to the column they are related to

Question

I've got a table setup like so:

[ReferredID], [Name]

1, Irwin

2, Marc

1, Johnny

5, Jackie

2, Perry

1, Reuben

I'm trying to get a query that will produce this:

[ReferredID], [List]

[1], [Irwin, Johnny, Reuben]

[2], [Marc, Perry]

[5], [Jackie]

Can you help me find the right query to produce these results or something near?

Answer 1

You might be able to do that with a specific flavour of SQL, but I don't think it's supported in any SQL standard.

If you're making SQL queries from some program, you're probably better off just doing a simple order by, and then grouping the records in your code.

In python, something like:

results = dict()
for r in rs:
    if r.ReferrerId not in results:
        results[r.ReferredId] = list()
    results[r.ReferredId].append(r.Name)

Answer 2

WITH    mytable (ReferredID, Name) AS
        (
        SELECT  1, 'Irwin'
        UNION ALL
        SELECT  2, 'Marc'
        UNION ALL
        SELECT  1, 'Johnny'
        UNION ALL
        SELECT  5, 'Jackie'
        UNION ALL
        SELECT  2, 'Perry'
        UNION ALL
        SELECT  1, 'Reuben'
        )
SELECT  ReferredID,
        (
        SELECT  CASE ROW_NUMBER() OVER (ORDER BY Name) WHEN 1 THEN '' ELSE ', ' END + Name AS [text()]
        FROM    mytable mi
        WHERE   mi.ReferredID = mo.ReferredID
        FOR XML PATH('')
        ) List
FROM    (
        SELECT  DISTINCT ReferredID
        FROM    mytable
        ) mo

Answer 3

This might help if you want to tackle this problem from the database side and if the limitations of XML path get in your way:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html