SELECT records as comma separated string [duplicate]

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.

user2989408
– user2989408

2014-06-18 15:24:26 +00:00
Commented Jun 18, 2014 at 15:24 — user2989408
– user2989408, Commented Jun 18, 2014 at 15:24

mohan111 · Accepted Answer · 2014-06-18 15:44:15Z

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, 2014 at 15:44

answered Jun 18, 2014 at 15:27

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SteveMustafa Over a year ago

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?

mohan111 Over a year ago

just put comma after t.lname and make sure to give correct name identifiers

SteveMustafa Over a year ago

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

Collectives™ on Stack Overflow

SELECT records as comma separated string [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related