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

Question

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          | 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?

Answer 1

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