This is in MS SSMS 2016. I have two tables - SHIPMENT_HEADER and SHIPMENT_DETAIL.
SHIPMENT_HEADER
shipment_id | customer |
------------|------------|
SH001 | cust1 |
SH002 | cust2 |
Shipment_detail has similar columns
SHIPMENT_DETAIL
shipment_id | customer | item | requested_qty|
------------|----------|--------|--------------|
SH001 | cust1 | item1 | 2 |
SH001 | cust1 | item2 | 2 |
SH001 | cust1 | item3 | 1 |
SH002 | cust2 | item1 | 2 |
SH002 | cust2 | item2 | 2 |
I'm wondering if it's possible at all to write something so that for every shipment_id, it returns the shipment_header details, with the shipment_details listed underneath, so:
shipment_id | instruction |customer | item | requested_qty|
------------|--------------|---------|--------|--------------|
SH001 | HEADER | cust1 | NULL | 5 |
SH001 | DETAIL | cust1 | item1 | 2 |
SH001 | DETAIL | cust1 | item2 | 2 |
SH001 | DETAIL | cust1 | item3 | 1 |
SH002 | HEADER | cust2 | NULL | 4 |
SH002 | DETAIL | cust2 | item1 | 2 |
SH002 | DETAIL | cust2 | item2 | 2 |
I thought maybe a UNION to select the header and then the details, but if I query multiple shipments at once, it selects all the headers then all the details, instead of header-details, header-details.
What I have so far is basically just selecting the each bit separately. This was written with UNION in mind, so some of the NULL columns are probably unneeded if another method is used
SELECT
SH.SHIPMENT_ID 'SHIPMENT_ID',
'HEADER' AS 'INSTRUCTION_TYPE',
SH.CUSTOMER 'CUSTOMER',
NULL 'ITEM',
B.TOTAL_QTY
FROM SHIPMENT_HEADER SH
INNER JOIN (
SELECT SHIPMENT_ID, SUM(REQUESTED_QTY) 'TOTAL_QTY'
FROM SHIPMENT_DETAIL
GROUP BY SHIPMENT_ID
) AS B
ON SH.SHIPMENT_ID = B.SHIPMENT_ID
WHERE SH.SHIPMENT_ID IN ('SH001','SH002')
UNION
SELECT
SD.SHIPMENT_ID,
'SHIPMENT_DETAIL' AS 'INSTRUCTION_TYPE',
SD.CUSTOMER,
SD.ITEM,
SD.REQUESTED_QTY
FROM SHIPMENT_DETAIL SD
WHERE SD.SHIPMENT_ID IN (
'SH001', 'SH002'
)
ORDER BY 1, 2
You can use the following statement :
(
SELECT header.shipment_id, 'HEADER' as 'instruction', header.customer, NULL as 'item', SUM(requested_qty) as 'requested_qty'
FROM SHIPMENT_HEADER header
LEFT JOIN SHIPMENT_DETAIL detail
ON header.shipment_id = detail.shipment_id
AND header.customer = detail.customer
GROUP BY header.shipment_id, header.customer
)
UNION
(
SELECT shipment_id, 'DETAIL', customer, item, requested_qty
FROM SHIPMENT_DETAIL
)
ORDER BY 1, 2 DESC, 3, 4
It gives the following results
shipment_id instruction customer item requested_qty
----------- ----------- ---------- ---------- -------------
SH001 HEADER cust1 NULL 5
SH001 DETAIL cust1 item1 2
SH001 DETAIL cust1 item2 2
SH001 DETAIL cust1 item3 1
SH002 HEADER cust2 NULL 4
SH002 DETAIL cust2 item1 2
SH002 DETAIL cust2 item2 2