Сложный T-SQL Query. Объединить несколько значений из столбца между основными / дочерними таблицами

Question

У меня есть три таблицы следующим образом:

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

+----------+-----------------------+
| MasterId | MasterId    | ChldId  |
+----------+-----------------------+
| 1        |  1          | 1       | 
| 2        |  2          | 1       | 
| 3        |  3          | 2       | 
| 4        |  4          | 3       | 
+----------+-----------------------+

Один ребенок может быть связан с несколькими мастерами, и LinkTable содержит эту деталь. Я хочу запрос, чтобы выбрать следующее:

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

Можно ли обойтись без циклов или вызова дополнительной функции, используя COALESCE, STUFFрекурсивный CTE так далее?

Answer 1

Чтобы объединить строки, вы можете использовать этот метод: Как объединить все строки из определенного столбца для каждой группы

Тестовые данные:

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)

Запрос:

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

---

Выход:

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

Answer 2

Хотя решение @polishchuk работает, у меня была своя собственная версия ниже:

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