sqlsql-server

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


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!


Solution

  • 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