Trying to reduce the number of rows in query results to 1 per works order number

Question

I'm trying to compile the results of a table I'm working on to only return 1 row per works order number. I'm sure there's something simple that I'm missing, but I just can't quite get it there.

Here's what I have so far:

DECLARE @PreviousStart SmallDateTime
DECLARE @PreviousEnd SmallDateTime
SET @PreviousStart = '11-01-2023 00:00:00'
SET @PreviousEnd = '11-30-2023 00:00:00'

Select WorksOrderNumber [Works Order Number],
Case When ProductKitManufacturedProcessID = 3 THEN AVOOptionTotalCostInCurrency END as [Truss Lumber],
Case When ProductKitManufacturedProcessID = 4 THEN AVOOptionTotalCostInCurrency END as [Truss Plates],
Case When ProductKitManufacturedProcessID = 5 THEN AVOOptionTotalCostInCurrency END as [Truss Labor],
Case When ProductKitManufacturedProcessID = 6 THEN AVOOptionTotalCostInCurrency END as [Truss Overhead]
from WorksOrderheader WOH
LEFT OUTER JOIN WorksOrderLine WOL on WOH.WorksOrderID = WOL.WorksOrderID AND WOL.ProductKitManufacturedProcessID is not null
Where BranchID = 1 AND
WorksOrderNumber != 0 AND
DateTimeCreated between @PreviousStart and @PreviousEnd AND
Description like '%Floor%'
Group by WorksOrderNumber, ProductKitManufacturedProcessID, AVOOptionTotalCostInCurrency
Order By WorksOrderNumber

And here are the results I'm getting:

Works Order Number	Truss Lumber	Truss Plates	Truss Labor	Truss Overhead
101782	NULL	NULL	NULL	1300
101782	3600	NULL	NULL	NULL
101782	NULL	2100	NULL	NULL
101782	NULL	NULL	3500	NULL

All I'm looking for is to get all 4 of these rows to be reflected in one row. The data it gives me is the data I'm looking for. The @PreviousStart and @PreviousEnd are just being used as filters for the smartview I'm using this code for in Bistrack, and those are fine. Just stuck on getting everything down to one row per works order.

Any and all help is appreciated!

Answer 1

You're basically describing a pivot of sorts. You have a bunch of values in essentially name/value format, and you want to get columns out of them. You said you wanted a single row per WorksOrderNumber so that's your first clue you need a group by WorksOrderNumber.

Now you need to project each of those 4 rows into their own columns. The way I like to do that is using a conditional max expression. Basically, I'm using max somewhat arbitrarily because I need some function to aggregate down to a single row. So I start with basically what you've already done, but throw a max around the case expression. That will eliminate all the null values, and leave you with the value you actually want.

Here's a pared down refactor of your code (I used iif, but that's essentially equivalent to case)

select
    [Works Order Number] = WorksOrderNumber,
    [Truss Lumber] = max(iif(ProductKitManufacturedProcessID = 3, AVOOptionTotalCostInCurrency, null)),
    [Truss Plates] = max(iif(ProductKitManufacturedProcessID = 4, AVOOptionTotalCostInCurrency, null)),
    [Truss Labor] = max(iif(ProductKitManufacturedProcessID = 5, AVOOptionTotalCostInCurrency, null)),
    [Truss Overhead] = max(iif(ProductKitManufacturedProcessID = 6, AVOOptionTotalCostInCurrency, null))
from WorksOrderheader WOH
left outer join WorksOrderLine WOL
    on WOH.WorksOrderID = WOL.WorksOrderID
        and WOL.ProductKitManufacturedProcessID is not null
group by WorksOrderNumber