I am trying get the following result:
At the moment, my query (and hopefully a guide to table structure) resembles:
WITH CoreGateway AS
(
SELECT g.GatewayTitle
, g.GatewayOrder
, ggs.GatewayGatewayStepId
FROM ref.Gateway g
JOIN ref.GatewayGatewayStep ggs ON ggs.GatewayId = g.GatewayId
JOIN ref.GatewayStep gs ON gs.GatewayStepId = ggs.GatewayStepId
WHERE gs.GatewayStepTitle = 'Carbon Calculated'
)
, CarbonCalc AS
(
SELECT pg.ProjectId
, pg.ProjectGatewayId
, pg.GatewayGatewayStepId
, cc.tC02
, cc.CarbonCost
FROM pmp.ProjectGateway pg
LEFT JOIN pmp.CarbonCalculated cc ON cc.ProjectGatewayId = pg.ProjectGatewayId
WHERE pg.ProjectId = 3021
)
, CarbonData AS
(
SELECT cc.ProjectId
, cg.GatewayTitle
, cg.GatewayTitle + '1' AS GatewayTitle1
, cg.GatewayOrder
, cc.tC02
, cc.CarbonCost
FROM CoreGateway cg
LEFT JOIN CarbonCalc cc ON cc.GatewayGatewayStepId = cg.GatewayGatewayStepId
)
SELECT p.ProjectId
, p.SolutionId
, p.NeedId
, p.ProjectCode
, p.ProjectDescription
, ISNULL([Gateway 1], 0.0) AS Gateway1_tC02
, ISNULL([Gateway 3], 0.0) AS Gateway3_tC02
, ISNULL([Gateway 5], 0.0) AS Gateway5_tC02
, ISNULL([Gateway 9], 0.0) AS Gateway9_tC02
, ISNULL([Gateway 11], 0.0) AS Gateway1_CarbonCost
, ISNULL([Gateway 31], 0.0) AS Gateway3_CarbonCost
, ISNULL([Gateway 51], 0.0) AS Gateway5_CarbonCost
, ISNULL([Gateway 91], 0.0) AS Gateway9_CarbonCost
FROM CarbonData cd
PIVOT (
MAX(tC02) FOR GatewayTitle IN ([Gateway 1], [Gateway 3], [Gateway 5], [Gateway 9])
) tc02
PIVOT (
MAX(CarbonCost) FOR GatewayTitle1 IN ([Gateway 11], [Gateway 31], [Gateway 51], [Gateway 91])
) tc02
JOIN pmp.Project p ON p.ProjectId = tc02.ProjectId
Which yields a result of:
As we can see, not all data has been entered in either ProjectGateway
or CarbonCalculated
, which is fine as they are optional. And the Pivot appears to be performing a Cartesian.
What is the best approach to pivot data for the 2 fields, when columns can be optional?
Best Answer
I eventually managed to get this working. Had to use a
Cross Apply
to flatten for the multiple pivots: