SQL return a comma separated value list

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 ou开发者_运维百科tput 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

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