SQL Server : flatten results from One to Many query

Question

好的，我有一个输入订单的系统，每个订单都可以附加任意数量的债券、工作和项目编号。 OrderDetails 表包含一个 orderID，用于转到 OrderNumbers 表并获取该订单的所有附加“数字”，然后根据在那里找到 numType 列，它会转到三个表(债券、作业、项目)之一并检索为用户显示和打印的实际数字。

这意味着，如果该订单附加了 3 个项目编号，则尝试查询订单以搜索特定项目编号可能会为同一订单返回 3 行。

我希望能够返回只有 1 行订单的查询结果，并且一列包含所有 3 个以分隔形式的项目，这可能吗？

这是我当前的查询，当订单附加多个相同类型的数字时，每个订单返回多行...

SELECT 
    ISNULL(b.Descr,'') as Bond, ISNULL(PO.Description,'') as PO, 
    ISNULL(j.Descr,'') as Job, ISNULL(Proj.Descr,'') as Project, 
    d.OrdNbr, d.BillofLadingNbr, d.TripAndDeliveryTicketNbr
FROM 
    OrderDetail d 
LEFT JOIN 
    OrderItemNumbers n ON d.OWID = n.LoadOWID 
LEFT JOIN 
    Bond b ON n.NumberOWID = b.OWID AND n.NumType = 0
LEFT JOIN 
    PONumbers PO ON n.NumberOWID = PO.OWID AND n.NumType = 1 
LEFT JOIN 
    Job j ON n.NumberOWID = j.OWID AND n.NumType = 2 
LEFT JOIN 
    Project Proj ON n.NumberOWID = Proj.OWID AND n.NumType = 3 
WHERE 
    d.OWID = 'BTBD1004'

这是我得到的结果......

Bond    PO              Job Project         OrdNbr      BillofLadingNbr
                            82001-8177-44   BTBD000063  BTBD000072  
        4.158 Kingsport                     BTBD000063  BTBD000072  
                            IME-81-1(118)   BTBD000063  BTBD000072  

这就是我想要得到的......(基本上展平前 4 列)

Bond    PO              Job Project                         OrdNbr      BillofLadingNbr
        4.158 Kingsport     82001-8177-44, IME-81-1(118)    BTBD000063  BTBD000072  

任何帮助表示赞赏!谢谢，道格

Answer 1

根据您的示例，Bond 和 Job 表上似乎没有匹配的记录。如果有的话，你会得到这样的东西:

BOND        PO              JOB      PROJECT        ORDNBR     BILLOFLADINGNBR  
Some bond                                           BTBD000063 BTBD000072
            4.158 Kingsport                         BTBD000063 BTBD000072
                            Some job                BTBD000063 BTBD000072
                                     82001-8177-44  BTBD000063 BTBD000072
                                     IME-81-1(118)  BTBD000063 BTBD000072

该结果集看起来非常“稀疏”，因为您直接将 OrderItemNumbers 连接到主查询中:该表对于每个子表都有一个不同的行(PONumbers，Job、Project)，这样您就可以将每个连接放在一个不同的、分隔的行中。为了避免这种情况，您可以将 OrderItemNumbers 与 each 子表联接起来，然后将此子查询与 OrderDetail 联接起来(通过共享 加载OWID):

SELECT
    ISNULL(b.Descr, '') as Bond,
    ISNULL(PO.Description, '') as PO, 
    ISNULL(j.Descr, '') as Job,
    ISNULL(Proj.Descr, '') as Project, 
    d.OrdNbr,
    d.BillofLadingNbr,
    d.TripAndDeliveryTicketNbr
FROM 
    OrderDetail d 
    LEFT JOIN (
        SELECT aux.*, n.*
        FROM Bond aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 0
    ) AS b
        ON b.LoadOWID = d.OWID
    LEFT JOIN (
        SELECT aux.*, n.*
        FROM PONumbers aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 1
    ) AS PO
        ON PO.LoadOWID = d.OWID
    LEFT JOIN (
        SELECT aux.*, n.*
        FROM Job aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 2
    ) AS j
        ON j.LoadOWID = d.OWID
    LEFT JOIN (
        SELECT aux.*, n.*
        FROM Project aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 3
    ) AS Proj
        ON Proj.LoadOWID = d.OWID
WHERE 
    d.OWID = 'BTBD1004'

这样你会得到以下结果:

Bond       PO               Job       Project         OrdNbr      BillofLadingNbr
Some bond  4.158 Kingsport  Some job  82001-8177-44   BTBD000063  BTBD000072  
Some bond  4.158 Kingsport  Some job  IME-81-1(118)   BTBD000063  BTBD000072

我同意这并不完全是您所要求的。您似乎还需要对 Project 表进行某种“部分旋转”(至少)。

理论上这也是可能的，但我不会那样做。这显然是一个表示需求，而在SQL层实现它的成本实在是太高了。

编辑

为了获得完整的平面结果集，您可以这样做(基于 this SO answer ):

DECLARE @bonds   VARCHAR(MAX)
DECLARE @numbers VARCHAR(MAX)
DECLARE @jobs    VARCHAR(MAX)
DECLARE @projs   VARCHAR(MAX)

DECLARE @owid VARCHAR(10) = 'BTBD1004'

SELECT
    @bonds = COALESCE(@bonds + ', ', '') + aux.Descr
FROM
    Bond aux
    INNER JOIN OrderItemNumbers n
        ON n.NumberOWID = aux.OWID AND n.NumType = 0
WHERE
    n.LoadOWID = @owid

SELECT
    @numbers = COALESCE(@numbers + ', ', '') + aux.Description
FROM
    PONumbers aux
    INNER JOIN OrderItemNumbers n
        ON n.NumberOWID = aux.OWID AND n.NumType = 1
WHERE
    n.LoadOWID = @owid

SELECT
    @jobs = COALESCE(@jobs + ', ', '') + aux.Descr
FROM
    Job aux
    INNER JOIN OrderItemNumbers n
        ON n.NumberOWID = aux.OWID AND n.NumType = 2
WHERE
    n.LoadOWID = @owid

SELECT
    @projs = COALESCE(@projs + ', ', '') + aux.Descr
FROM
    Project aux
    INNER JOIN OrderItemNumbers n
        ON n.NumberOWID = aux.OWID AND n.NumType = 3
WHERE
    n.LoadOWID = @owid

SELECT
    @bonds,
    @numbers, 
    @jobs,
    @projs,
    d.OrdNbr,
    d.BillofLadingNbr,
    d.TripAndDeliveryTicketNbr
FROM 
    OrderDetail d 
WHERE 
    d.OWID = @owid

这将导致:

Bond       PO               Job       Project                        OrdNbr      BillofLadingNbr
Some bond  4.158 Kingsport  Some job  82001-8177-44, IME-81-1(118)   BTBD000063  BTBD000072  

如果您也为同一个 OWID 获得许多债券 x 许多 PO x 许多工作，那么它就会起作用。

恕我直言，这很好地说明了我想说的:是的，它满足了您的需要，但它丑陋得要命。当然，如果您不在乎，请继续(只需添加一些触发器，您就会感觉又回到了 1995 年 :P )。

希望有帮助。