SELECT records as comma separated string

Question

So I have the following tables:

Table 1

fname    mi    lname    empid

John     A     Smith    1202
Bob            Doe      9815


Table 2

unid    empid

1015     1202
1015     9815


Table 3

unid    Item

1015    ABC
1015    DEF

My intended output should be (when supplying unid=1015)

fname    mi    lname    item

John     A     Smith    ABC, DEF
Bob            Doe

Now that would be the ideal, but I'm more than happy to deal with the repeated [Item] values on the front end.

My current statement is:

select p.FNAME,p.MI,p.LNAME, ac.EQUIP from table1 t1, table2 t2, table3 t3  
where t1.EMPID = t2.EMPID and t2.UNID = t3.UNID and t2.unid = '1015' group by t1.FNAME, t1.MI, 
t1.LNAME,t3.EQUIP

For the life of me, I cannot figure out how get the values in item (which can be 0 or more to a maximum of 8) as one comma separated string. My problem is, due to site/client constraints, I cannot use an SP but this has to be done in one SQL statement.

This is on SQL SERVER 2008 R2.

You have to use [STUFF](http://stackoverflow.com/questions/12671117/comma-separated-values-with-sql-query). — user2989408, Jun 18 '14 at 15:24

mohan111 · Accepted Answer · 2014-06-18T15:44:15.257

1

Select distinct t.fname,t.mi,t.lname,
    STUFF((Select distinct i.item +  ',' 
        from Table3 i 
    where t.unid = tt.unid AND i.unid = '1015' 
    ORDER BY i.unid
    FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)') 
                        , 1,  0, ' ') from Table1 t
                        INNER JOIN Table2 tt
                        ON tt.empid = t.empid
                     group by
                            t.FNAME, 
                            t.MI,
                            t.LNAME

edited Jun 18 '14 at 15:44

answered Jun 18 '14 at 15:27

mohan111

8,633
4
28
55

What black magic is this? Thank you for introducing me to something I have NEVER seen before. 1 quick question because I'm having problems getting this to work as is, does the stuff clause need to come after a comma in this case? – SteveMustafa Jun 18 '14 at 15:38
just put comma after t.lname and make sure to give correct name identifiers – mohan111 Jun 18 '14 at 15:45
As a quick follow up, how on earth would I get rid of the trailing comma? This is legacy software and I'm very limited in what I can do – SteveMustafa Jun 18 '14 at 21:59

SELECT records as comma separated string

1 Answers1