SQL return a comma separated value list

Question

There are three tables as given below. (I have coded them with data insertions for ease). I need to obtain ValueB in a comma separated list for each row in @TableA

DECLARE @TableA TABLE(IDA INT, ValueA VARCHAR(20))
DECLARE @TableB TABLE(IDB INT, ValueB VARCHAR(20))
DECLARE @TableC TABLE(IDC INT, fIDA INT, fIDB INT)

INSERT INTO @TableA(IDA,ValueA)
SELECT 1, 'aaa'
UNION 
SELECT 2, 'bbb'
UNION 
SELECT 3, 'ccc'
UNION
SELECT 4, 'ddd'


INSERT INTO @TableB(IDB, ValueB)
SELECT 1, 'Option1'
UNION 
SELECT 2, 'Option2'
UNION
SELECT 3, 'Option3'


INSERT INTO @TableC(IDC,fIDA,fIDB)
SELECT 1,1,1
UNION
SELECT 2,1,3
UNION
SELECT 3,3,1
UNION
SELECT 4,3,2
UNION
SELECT 5,4,3

Expected output is:

IDA  | ValueA| Options
-----------------------------------
1   | aaa   | Option1,Option3
2   | bbb   | NULL
3   | ccc   | Option1,Option2
4   | ddd   | Option3

Using TSQL in SQLServer 2005 how can I obtain the above expected output

-1 When you posted this question did you even look at any of the "related questions" the system showed you? — Martin Smith, Feb 13 '11 at 13:34
possible duplicate of [Building a comma separated list?](http://stackoverflow.com/questions/1564980/building-a-comma-separated-list) — Martin Smith, Feb 13 '11 at 13:37
I am bit new to TSQL. I did look at "related questions" I didn't find a matching solution to my problem. — Dimuthu, Feb 13 '11 at 13:37
@Dimuthu - I actually was a bit harsh there. This question does come up at least once a day on SO and has loads of duplicates! I was going to retract my down vote as at least your question does have useful DDL but my vote is locked in now. You should find the answer in my link above. Or this one might be more obviously comparable http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 — Martin Smith, Feb 13 '11 at 13:39
@Martin - Thanks for your help. Appreciate your quick feedback. I found my self the answer to my specific problem after looking at [link](http://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query) — Dimuthu, Feb 13 '11 at 17:41
What interests me is why is this "problem" so widespread? What are all those comma separated lists used for? :) — Goran, Feb 13 '11 at 17:50
@Goran: Maybe there's already a question about this. If not, you may consider posting one yourself. I'm not as curious as you about this, probably, but the question seems to me valid anyway. — Andriy M, Feb 13 '11 at 20:16
@Andriy - and it will get shunted to programmers.se very quickly — RichardTheKiwi, Feb 14 '11 at 03:02
@cyberkiwi: Yeah, I didn't think about that. But then, programmers seems to be a more appropriate place for this kind of question. Only... What with so many questions about string concatenation in SQL here, it would be reasonable to want to address the same public with that 'why' question. And I wonder if there are many people on SO who visit programmers as well. :\ — Andriy M, Feb 14 '11 at 04:26
@Andriy I didn't, but every now and then a question gets shunted there, and you become a de-facto member — RichardTheKiwi, Feb 14 '11 at 04:34

score 3 · Accepted Answer · answered Feb 13 '11 at 17:45

This is the solution to above question:

SELECT ta.IDA, ta.ValueA, stuff((
    SELECT ', ' + cast(ValueB as varchar(max))
    FROM @TableB tb INNER JOIN @TableC tc ON tc.fIDB = tb.IDB
    WHERE tc.fIDA = ta.IDA
    FOR XML PATH('')
    ), 1, 2, '') AS Options
FROM @TableA ta

SQL return a comma separated value list

1 Answers1