SQL Multiple Rows to one CSV

Question

I'm having issues converting concatenating multiple rows to one row with CSV

This is what I have

   [Project Number] | [Resources]
1   25254              , Jim Anderson
2   25254              , Becky Smith
3   32564              , Amy Dickerson
4   32564              , James Walsh
5   25679              , Jim Anderson
6   25679              , James Walsh

This is what I need

   [Project Number] | [Resources]
1   25254              Jim Anderson, Becky Smith
2   32564              Amy Dickerson, James Walsh
3   25679              Jim Anderson, James Walsh

This is the query I'm using

select *
from(
        select
            'Project Number' = p.ProjectID
        ,   'Resources' = ', ' + pe.Name
        from
            Projects p (nolock)
        left join Persons pe (nolock) on p.PersonsID = pe.PersonsID
        where
            year( p.CreatedDTM) = 2013
            and p.TypeID = 1
    ) ta
order by ta.APAID

Please help.

Thank you.

Try adding double quotes to your resources col. – user2989408 Jan 31 '14 at 17:50 — user2989408, Jan 31 '14 at 17:50

score 0 · Accepted Answer · answered Jan 31 '14 at 17:51

select [Project Number],
    stuff((select ',' + CAST(t2.Resources as varchar(10))
     from t t2 where t1.[Project Number]= t2.[Project Number]
     for xml path('')),1,1,'') Resources
from t t1
group by [Project Number]

Output:

| PROJECT NUMBER |            Resources |
|----------------|-----------------------|
|          25254 | Jim Anders,Becky Smit |
|          25679 | Jim Anders,James Wals |
|          32564 | Amy Dicker,James Wals |

SQL FIDDLE

SQL Multiple Rows to one CSV

1 Answers1