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?

Why your `LinkTable` table contains two duplicate `MasterId` column? — S.Serpooshan, Dec 12 '18 at 07:09
@S.Serpooshan probably meant to be Id for this table. Don't even remember this now! — TheVillageIdiot, Dec 12 '18 at 22:00

score 3 · Accepted Answer · edited May 23 '17 at 11:48

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

score 1 · Answer 2 · answered Jul 13 '11 at 04:38

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

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

2 Answers2