SQL Server : flatten results from One to Many query
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
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.