Pivot Multiple Columns with a Cross Apply 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?
I eventually managed to get this working. Had to use a Cross Apply to flatten for the multiple pivots:

WITH CoreGateway AS
(
SELECT      pg.ProjectId
,           g.GatewayTitle
,           ggs.GatewayGatewayStepId
,           cc.tC02
,           cc.CarbonCost
FROM        pmp.ProjectGateway      pg
LEFT JOIN   pmp.CarbonCalculated    cc  ON  cc.ProjectGatewayId         = pg.ProjectGatewayId
JOIN        ref.GatewayGatewayStep  ggs ON  ggs.GatewayGatewayStepId    = pg.GatewayGatewayStepId
JOIN        ref.Gateway             g   ON  g.GatewayId                 = ggs.GatewayId
JOIN        ref.GatewayStep         gs  ON  gs.GatewayStepId            = ggs.GatewayStepId
WHERE       gs.GatewayStepTitle     = 'Carbon Calculated'
)
, CarbonData AS
(
SELECT      cg.ProjectId
,           cg.GatewayTitle + CarbonType AS GatewayTitle
,           CarbonValue
FROM        CoreGateway     cg
CROSS APPLY (
            VALUES  (cg.tC02, 'tC02')
            ,       (cg.CarbonCost, 'CC')
            ) c (CarbonValue, CarbonType)
)
SELECT      p.ProjectId
,           p.SolutionId
,           p.NeedId
,           p.ProjectCode
,           p.ProjectDescription
,           ISNULL([Gateway 1CC], 0.0)      AS Gateway1_CarbonCost
,           ISNULL([Gateway 3CC], 0.0)      AS Gateway3_CarbonCost
,           ISNULL([Gateway 3CC], 0.0) -
            ISNULL([Gateway 1CC], 0.0)      AS Gateway3_CarbonCost_Diff
,           ISNULL([Gateway 5CC], 0.0)      AS Gateway5_CarbonCost
,           ISNULL([Gateway 5CC], 0.0) -
            ISNULL([Gateway 1CC], 0.0)      AS Gateway5_CarbonCost_Diff
,           ISNULL([Gateway 9CC], 0.0)      AS Gateway9_CarbonCost
,           ISNULL([Gateway 9CC], 0.0) -
            ISNULL([Gateway 1CC], 0.0)      AS Gateway9_CarbonCost_Diff
,           ISNULL([Gateway 1tC02], 0.0)    AS Gateway1_tC02
,           ISNULL([Gateway 3tC02], 0.0)    AS Gateway3_tC02
,           ISNULL([Gateway 3tC02], 0.0) -
            ISNULL([Gateway 1tC02], 0.0)    AS Gateway3_tC02_Diff
,           ISNULL([Gateway 5tC02], 0.0)    AS Gateway5_tC02
,           ISNULL([Gateway 5tC02], 0.0) -
            ISNULL([Gateway 1tC02], 0.0)    AS Gateway5_tC02_Diff
,           ISNULL([Gateway 9tC02], 0.0)    AS Gateway9_tC02
,           ISNULL([Gateway 9tC02], 0.0) -
            ISNULL([Gateway 1tC02], 0.0)    AS Gateway9_tC02_Diff
FROM        (
            SELECT  cd.ProjectId
            ,       cd.GatewayTitle
            ,       cd.CarbonValue
            FROM    CarbonData  cd
            )   cc
PIVOT       (
            MAX(CarbonValue) FOR GatewayTitle IN    (   [Gateway 1CC], [Gateway 3CC], [Gateway 5CC], [Gateway 9CC]
                                                    ,   [Gateway 1tC02], [Gateway 3tC02], [Gateway 5tC02], [Gateway 9tC02]
                                                    )
            ) carpvt
JOIN        pmp.Project p   ON p.ProjectId = carpvt.ProjectId;