Aggregate function to concatenate strings joining two tables?

Question

I know how to concatenate fields on aggregate query using for xml auto. But I've some problems to extend that query to use with two tables, the following example does not work

E.g.

set nocount on;
declare @FirstTable table (RowID int, LinkedField int)
insert into @FirstTable VALUES (1,1)
insert into @FirstTable VALUES (2,2)
insert into @FirstTable VALUES (2,3)
insert into @FirstTable VALUES (2,4)
insert into @FirstTable VALUES (3,5)
insert into @FirstTable VALUES (3,1)

declare @SecondTable table (LinkedField int, Field2Show int)
insert into @SecondTable VALUES (1,11)
insert into @SecondTable VALUES (2,12)
insert into @SecondTable VALUES (3,13)
insert into @SecondTable VALUES (4,14)
insert into @SecondTable VALUES (5,15)

/*
output i need:

RowID       ChildValues
----------- -------------------
1           11
2           12,13,14
3           11,15
*/

SELECT t1.RowID,
STUFF(
           ( SELECT ', ' + t2.Field2Show
            FROM
              ( SELECT @SecondTable.Field2Show, @SecondTable.LinkedField
               FROM @SecondTable
               INNER JOIN @FirstTable ON         @FirstTable.LinkedField=@SecondTable.LinkedField ) t2
            WHERE t1.LinkedField = t2.LinkedField
            ORDER BY t2.Field2Show
            FOR XML PATH(''), TYPE).value('.','varchar(max)') ,1,2, '') AS ChildValues
FROM @FirstTable t1
GROUP BY t1.RowID

Answer 1

You should be able to alter the question to the following to get the final result:

SELECT distinct t1.RowID,
STUFF(
           (SELECT ', ' + cast(t2.Field2Show as varchar(10))
            FROM @SecondTable t2
            INNER JOIN @FirstTable t  -- join on @FirstTable based on LinkedField
              on t.LinkedField = t2.LinkedField
            WHERE t1.RowID = t.RowID -- associate the RowId from FirstTable to concatenate
            ORDER BY t2.Field2Show
            FOR XML PATH(''), TYPE).value('.','varchar(max)') ,1,2, '') AS ChildValues
FROM @FirstTable t1;

See SQL Fiddle with Demo