TSQL Pivot Throwing Syntax Errors

Question

I would like to PIVOT the following query result to display a column for each Project Status Code.

WITH r AS (
    SELECT ROW_NUMBER() OVER (ORDER BY ph.InsertedDateTime) rownum,
        CAST(ph.InsertedDateTime AS DATE) InsertedDate, ph.Gate_1_TargetDate, ph.Gate_2_TargetDate, ph.Gate_3_TargetDate
    FROM PROJECT_HIST ph
    JOIN (
        SELECT ProjectID, MAX(InsertedDateTime) InsertedDateTime
        FROM PROJECT_HIST
        GROUP BY ProjectID, CAST(InsertedDateTime AS DATE)
    ) ph_distinct_date ON ph_distinct_date.InsertedDateTime = ph.InsertedDateTime
        AND ph_distinct_date.ProjectID = ph.ProjectID
    WHERE ph.projectid = 100957
        AND NOT (
            ph.Gate_1_TargetDate IS NULL
                AND ph.Gate_2_TargetDate IS NULL
                AND ph.Gate_3_TargetDate IS NULL
        )
),
fubar AS (
    SELECT rownum, InsertedDate, 0 gateName, NULL targetDate FROM r
    UNION ALL
    SELECT rownum, InsertedDate, 1, Gate_1_TargetDate FROM r
    UNION ALL
    SELECT rownum, InsertedDate, 2, Gate_2_TargetDate FROM r
    UNION ALL
    SELECT rownum, InsertedDate, 3, Gate_3_TargetDate FROM r
)
SELECT f1.InsertedDate 'Change Date', f1.gateName 'ProjectStageCode', f1.targetDate
FROM fubar f1
LEFT JOIN fubar f2 ON f2.rownum = f1.rownum - 1
    AND f2.gateName = f1.gateName
PIVOT(min(f1.InsertedDate) FOR f1.gateName IN ([0],[1],[2],[3])) AS p
WHERE f1.rownum = 1
    OR f1.targetDate <> f2.targetDate
ORDER BY f1.InsertedDate
;

Without the pivot attempt, this query currently returns this result for this particular project:

What I would like to do is pivot the query to create columns for each Project Stage Code to match the following result:

Essentially, I need to have a row for each unique Change Date and have the targetDate column value fill in the respective newly pivoted numerical ProjectStageCode column.

Answer 1

From the looks of it it seems like you just need to use a subquery before you try to PIVOT the data. You also need to aggregate the targetDate instead of the InsertedDate:

WITH r AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY ph.InsertedDateTime) rownum,
        CAST(ph.InsertedDateTime AS DATE) InsertedDate, ph.Gate_1_TargetDate, ph.Gate_2_TargetDate, ph.Gate_3_TargetDate
    FROM PROJECT_HIST ph
    JOIN 
    (
        SELECT ProjectID, MAX(InsertedDateTime) InsertedDateTime
        FROM PROJECT_HIST
        GROUP BY ProjectID, CAST(InsertedDateTime AS DATE)
    ) ph_distinct_date 
        ON ph_distinct_date.InsertedDateTime = ph.InsertedDateTime
        AND ph_distinct_date.ProjectID = ph.ProjectID
    WHERE ph.projectid = 100957
        AND NOT (ph.Gate_1_TargetDate IS NULL
                    AND ph.Gate_2_TargetDate IS NULL
                    AND ph.Gate_3_TargetDate IS NULL)
),
fubar AS 
(
    SELECT rownum, InsertedDate, 0 gateName, NULL targetDate FROM r
    UNION ALL
    SELECT rownum, InsertedDate, 1, Gate_1_TargetDate FROM r
    UNION ALL
    SELECT rownum, InsertedDate, 2, Gate_2_TargetDate FROM r
    UNION ALL
    SELECT rownum, InsertedDate, 3, Gate_3_TargetDate FROM r
)
SELECT ChangeDate, [0],[1],[2],[3]
FROM
(
    SELECT f1.InsertedDate ChangeDate, f1.gateName, f1.targetDate
    FROM fubar f1
    LEFT JOIN fubar f2 
        ON f2.rownum = f1.rownum - 1
        AND f2.gateName = f1.gateName
    WHERE f1.rownum = 1
        OR f1.targetDate <> f2.targetDate
) d
PIVOT
(
    min(targetDate) 
    FOR gateName IN ([0],[1],[2],[3])
) AS p;