SQL Server : flatten results from One to Many query

Question

OK, I have a system where orders get entered, and each order can have any number of bond, job and project numbers attached to it. The OrderDetails table contains an orderID used to go to the OrderNumbers table and get all attached "numbers" to this order, and then based on a numType column found there, it goes to one of the three tables (bond, job, project) and retrieves the actual number displayed and printed for the user.

This means that trying to query an order to search for a certain project number can return 3 rows for the same order if that order had 3 project numbers attached to it.

I am looking to be able to return my query results with only 1 row for the order, and a column which includes all 3 projects in delimited form, is this possible?

Here is my current query which returns multiple rows per order, when multiple numbers of the same type are attached to the order...

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'

and here are the results I get....

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

and here is what I am trying to get...(basically flattening those first 4 columns)

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

Any help is appreciated!!! Thanks, Doug

Answer 1

By your example, it seems that there are no matching records on Bond and Job tables. If there were, you would get something like this:

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

That result set seems so "sparse" because you were joining OrderItemNumbers directly into your main query: this table has a distinct row for each child table (PONumbers, Job, Project), and so you get each join in a distinct, separated row. In order to avoid that, you could join OrderItemNumbers with each child table, and then join this subquery with OrderDetail (through the shared LoadOWID):

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'

That way you will get the following result:

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

I agree this is not exactly what you asked for, yet. You also seem to need some sort of "partial pivoting" over the Project table (at least).

That also would be possible, in theory, but I wouldn't go that way. This is clearly a presentation requirement, and the cost to implement it on the SQL layer is simply too high.

Edit

In order to get a complete flat result set, you can do that (based on 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

Which will result in this:

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

It works if you get many bonds x many PO's x many jobs for the same OWID too.

IMHO that illustrates quite well what I tried to say: yes, it does what you need, but it is ugly as hell. Of course, if you don't care, go ahead (just add some triggers you'll feel like it is 1995 again :P ).

Hope it helps.