Tricky T-SQL Query. Concatenate multiple values from a column between master/child tables

I have three tables as follows:

MasterTable

+----------+-------------+
| MasterId | MasterName  |
+----------+-------------+
| 1        | Master 1    |
| 2        | Master 2    |
| 3        | Master 3    |
| 4        | Master 4    |
+----------+-------------+

ChildrenTable

+----------+-------------+
| ChildId | ChildName    |
+----------+-------------+
| 1        | Child 1     |
| 2        | Child 2     |
| 3        | Child 3     |
| 4        | Child 4     |
+----------+-------------+

LinkTable

+----------+-----------------------+
| Id       | MasterId    | ChldId  |
+----------+-----------------------+
| 1        |  1          | 1       | 
| 2        |  2          | 1       | 
| 3        |  3开发者_如何学Go          | 2       | 
| 4        |  4          | 3       | 
+----------+-----------------------+

One child can be linked with multiple masters and LinkTable contains this detail. I want a query to select the following:

1, 'Child 1', 'Master 1, Master 2', '1,2'
2, 'Child 2', 'Master 2', '2'
3, 'Child 3', 'Master 3', '3'

Is it possible to do without loops or calling additional function, using COALESCE, STUFF, recursive CTE etc?

To concatenate strings you can use this method: How to concatenate all strings from a certain column for each group

Test data:

declare @masterTable table(MasterId int identity, MasterName varchar(max))
insert @masterTable (MasterName) values('m1'), ('m2'), ('m3'), ('m4')

declare @childrenTable table(ChildId int identity, ChildName varchar(max))
insert @childrenTable (ChildName) values('c1'), ('c2'), ('c3'), ('c4')

declare @LinkTable table(MasterId1 int, MasterId2 int, ChildId int)
insert @LinkTable values(1,1,1), (2,2,1), (3,3,2), (4,4,3)

Query:

select t.*
from
(
    select c.ChildId, c.ChildName

        , STUFF((
            select ', ' + m.MasterName
            from
            (
                select l.MasterId1
                from @LinkTable l
                where l.ChildId = c.ChildId

                union

                select l.MasterId2
                from @LinkTable l
                where l.ChildId = c.ChildId
            )t
            join @masterTable m on m.MasterId = t.MasterId1
            for xml path(''), type
        ).value('.', 'varchar(max)'), 1, 2, '') [names]

        , STUFF((
            select ', ' + cast(t.MasterId1 as varchar(max))
            from
            (
                select l.MasterId1
                from @LinkTable l
                where l.ChildId = c.ChildId

                union

                select l.MasterId2
                from @LinkTable l
                where l.ChildId = c.ChildId
            )t
            for xml path(''), type
        ).value('.', 'varchar(max)'), 1, 2, '') [ids]
    from @childrenTable c
)t
where t.ids is not null

Output:

----------- --- -------- ------
1           c1  m1, m2   1, 2
2           c2  m3       3
3           c3  m4       4

Though @polishchuk's soluition works, I had my own version below:

SELECT  ChildId, ChildName, ISNULL(mastDetail.MasterIds,'')MasterIds, 
        ISNULL(mastDetail.MasterNames, '') MasterNames
FROM    ChildrenTable sub
OUTER APPLY
(
    SELECT 
    STUFF( 
            (SELECT  ',' + mast.MasterName
             FROM    MasterTable mast
             INNER JOIN LinkTable link ON (mast.MasterId = link.MasterId AND 
                   link.ChildId = child.ChildId)
             FOR XML PATH('')
            ), 1,1,''
        ) AS MasterNames,
    STUFF( 
            (SELECT  ',' + CAST(mast.MasterId AS VARCHAR)
             FROM    MasterTable mast
             INNER JOIN LinkTable link ON (mast.MasterId = link.MasterId AND 
             link.ChildId = child.ChildId)
             FOR XML PATH('')
            ), 1,1,''
        ) AS MasterIds
) AS mastDetail