Join multiple pivot tables

[radiancebox]

I'm using MS SQL 2012 - trying to LEFT JOIN 3 Pivots - but am running into issues within a query variable that I'm not sure how to debug. Each Pivot works on it's own - but the actual Joining is causing the issue. Any help is appreciated. First time using Pivot - so it may be a minor fix. Have been Googling/Trial&Error for a good day and a half without luck. SQL Fiddle loaded with my code. Thanks in advance! :)

The Result Columns

ID | BaseImpact1_Actual | ... | BaseImpactN_Actual | BaseImpact1_Update | ... | BaseImpactN_Update | BaseImpact1_Plan | ... | BaseImpactN_Plan

SQL Sample Data

create table BaseImpact
(
    BaseImpactID int,
    BaseImpactName varchar(50)
)

insert into BaseImpact values (0, 'Base Impact 0')
insert into BaseImpact values (1, 'Base Impact 1')
insert into BaseImpact values (2, 'Base Impact 2')
insert into BaseImpact values (3, 'Base Impact 3')

create table Impact
(
    ImpactID int,
    ActualValue int,
    PlanValue int,
    UpdateValue int,
    BaseImpactID int
)

insert into Impact values (0, 3, 5, 8, 0)
insert into Impact values (1, 6, 2, 4, 1)
insert into Impact values (2, 2, 1, 9, 1)
insert into Impact values (3, 1, 3, 0, 1)
insert into Impact values (4, 0, 5, 8, 1)
insert into Impact values (5, 9, 4, 3, 1)
insert into Impact values (6, 3, 5, 8, 2)
insert into Impact values (7, 6, 2, 4, 2)
insert into Impact values (8, 2, 1, 9, 2)
insert into Impact values (9, 1, 3, 0, 2)
insert into Impact values (10, 0, 5, 8, 3)
insert into Impact values (11, 9, 4, 3, 3)

SQL Query

DECLARE @colsActual AS NVARCHAR(MAX)
DECLARE @colsUpdate AS NVARCHAR(MAX)
DECLARE @colsPlan AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)


select @colsActual = STUFF((SELECT distinct ',' + QUOTENAME(BaseImpactName + '_Actual') 
                    from BaseImpact
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsUpdate = STUFF((SELECT distinct ',' + QUOTENAME(BaseImpactName + '_Update') 
                    from BaseImpact
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsPlan = STUFF((SELECT distinct ',' + QUOTENAME(BaseImpactName + '_Plan') 
                    from BaseImpact
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT * FROM 
                 ((SELECT i.NodeID
                        , i.ActualValue
                        , b.BaseImpactName
                    FROM Impact as i
                    INNER JOIN BaseImpact as b
                        ON i.RoadmapBaseImpactMapID = b.BaseImpactID
                    ) x
                    PIVOT 
                    (
                        MIN(ActualValue)
                        FOR BaseImpactName IN (' + @colsActual + ')
                    ) as pvt) as actual

                LEFT JOIN 
                     ((SELECT i.NodeID
                            , i.UpdateValue
                            , b.BaseImpactName
                        FROM Impact as i
                        INNER JOIN BaseImpact as b
                            ON i.RoadmapBaseImpactMapID = b.BaseImpactID
                    ) x
                    PIVOT 
                    (
                        MIN(UpdateValue)
                        FOR BaseImpactName IN (' + @colsUpdate + ')
                    ) as pvt) as update
                ON actual.NodeID = update.NodeID

                LEFT JOIN 
                    ((SELECT i.NodeID
                            , i.PlanValue
                            , b.BaseImpactName
                        FROM Impact as i
                        INNER JOIN BaseImpact as b
                            ON i.RoadmapBaseImpactMapID = b.BaseImpactID
                    ) x
                    PIVOT 
                    (
                        MIN(PlanValue)
                        FOR BaseImpactName IN (' + @colsPlan + ')
                    ) as pvt) as plan
                ON actual.NodeID = plan.NodeID'


execute(@query)

[kgariando]

You did not specify what RDBMS you are using but there are several ways that you can transform this data into the result that you need.

If you are using a database that does not have a PIVOT function, then this can be done by using a UNION ALL query to unpivot the data and then use an aggregate function with a CASE expression to pivot the dates into columns.