Zack Scriven Zack Scriven - 3 months ago 18
SQL Question

Sum a Single column into multiple columns based on criteria SQL Server

Right now I am collecting a sum of times based on grouping by a part, job, machine, and type.

I would like to have the summed times to be split across multiple columns rather than multiple rows based on the type. How can I do this?

Here is my code

SELECT
m.[machineName]
,pr.[jobNumber]
,p.[partNumber]
,sc.[type]
,SUM(pl.[elapsedTime]) AS elapsedTime
FROM wincc.dbo.productionLog pl
INNER JOIN wincc.dbo.machines m ON pl.[machineId] = m.id
INNER JOIN wincc.dbo.productionRuns pr ON pl.[productionRunId] = pr.id
INNER JOIN wincc.dbo.parts p ON pr.[partId] = p.Id
INNER JOIN wincc.dbo.statusCodes sc ON pl.[statusCodeId] = sc.id
GROUP BY
m.[machineName]
,pr.[jobNumber]
,p.[partNumber]
,sc.[type]


Which produces:

enter image description here

But I want:

enter image description here

Thank you All!

Answer

This is a form of table pivoting. Here's one way to do that with conditional aggregation:

SELECT
    m.[machineName]
    ,pr.[jobNumber]
    ,p.[partNumber]
    ,SUM(CASE WHEN sc.Type = 'planned downtime' then pl.[elapsedTime] END) AS plannedDT
    ,SUM(CASE WHEN sc.Type = 'unplanned downtime' then pl.[elapsedTime] END) AS unplannedDT
    ,SUM(CASE WHEN sc.Type = 'production' then pl.[elapsedTime] END) AS production
    ,SUM(CASE WHEN sc.Type = 'rework' then pl.[elapsedTime] END) AS rework
FROM wincc.dbo.productionLog pl
    INNER JOIN wincc.dbo.machines m        ON pl.[machineId]       = m.id
    INNER JOIN wincc.dbo.productionRuns pr ON pl.[productionRunId] = pr.id
    INNER JOIN wincc.dbo.parts p           ON pr.[partId]          = p.Id
    INNER JOIN wincc.dbo.statusCodes sc    ON pl.[statusCodeId]    = sc.id
GROUP BY
    m.[machineName]
    ,pr.[jobNumber]
    ,p.[partNumber]