时间戳

首页 > 解决方案 > 在单行上显示相关子记录

tsql - 在单行上显示相关子记录

问题描述

在我熟悉的数据库中，列出父母和孩子的查询如下所示：

Parfirst ParLast Childfirst
Mary Smith Sally
Mary Smith Jim
Mary Smith Kim

但是，我被要求创建一个如下所示的报告：

Parfirst ParLast Child1 Child2 Child3 Child4
Mary Smith Sally Jim Kim

我不知道如何做到这一点。欢迎任何建议。

标签： tsql

解决方案

您可以使用STRING_AGG函数（如果您使用的是 SQL Server 2017 或更高版本）或使用FOR XML来做一些丑陋的 XML 魔术：

SELECT t.ParFirst, t.ParLast, LEFT(t.Children, Len(t.Children)-1) As Children
FROM
    (
        SELECT DISTINCT p.ParFirst, p.ParLast, 
            (
                SELECT c.ChildFirst + ',' AS [text()]
                FROM dbo.Children c
                WHERE c.ParentId = p.ParentId
                ORDER BY c.ChildFirst
                FOR XML PATH ('')
            ) l
        FROM dbo.Parents p
    ) t

SELECT p.ParFirst, p.ParLast, STRING_ACC(c.ChildFirst, ',') as Children
FROM dbo.Parents p
LEFT JOIN dbo.Children c on c.ParentId = p.ParentId
GROUP BY p.ParFirst, p.ParLast

如果要将每个子名称包含在它自己的列中，则必须在查询中定义这些列，并且如果列数超过您定义的列数，您将无法返回任何其他子列。像这样使用ROW_NUMBER：

select p.ParFirst, p.ParLast, c1.ChildFirst, c2.ChildFirst, c3.ChildFirst
from dbo.Parents p
outer apply ( select ChildFirst from (select ChildFirst, row_number() over  (order by ChildFirst) as rowNo from dbo.Children c where c.ParentId = p.ParentId) t1 where t1.rowNo = 1 )  c1
outer apply ( select ChildFirst from (select ChildFirst, row_number() over(order by ChildFirst) as rowNo from dbo.Children c where c.ParentId = p.ParentId) t2 where t2.rowNo = 2 )  c2
outer apply ( select ChildFirst from (select ChildFirst, row_number() over(order by ChildFirst) as rowNo from dbo.Children c where c.ParentId = p.ParentId) t3 where t3.rowNo = 3 )  c3

推荐阅读