SQL Server 2012 Pivot Table

Question

I need to create a query that shows the results in a pivot table.

I have a table that is updated regularly with clients Build Status Changes

WE have 8 stages to a build

115 Land Purchased
116 Foundations
117 Timber Kit Erected / Wall Plate Level
118 Wind & Water Tight
119 1st Fix & Plastering
120 Final Fit Out
121 Completed
122 Redeemed

Here is my query

SELECT 
    s.ProjectStage, su.DateStageChanged, p.FK_ID As ID,
    s.LongDesc AS BuildType, su.FK_StageID  
FROM 
    [dbo].[tbl_StageUpdates] AS su
LEFT JOIN
    [dbo].[tbl_Stage] AS s ON su.FK_StageID = s.id
LEFT JOIN 
    tbl_Projects AS p ON su.FK_ProjectID = p.PK_ProjectID
WHERE 
    s.LongDesc = 'New Build'
GROUP BY 
    p.FK_ID, s.ProjectStage, su.DateStageChanged, s.LongDesc, su.FK_StageID
ORDER BY 
    su.FK_StageID ASC

ID is the clients ID what i would like is have the query display the information like this:

Is it possible to do?

Any help on this would be greatly appreciated

Cheers

Trung Duong · Accepted Answer · 2017-06-26T12:38:38.467

2

If you've only 8 stages, you could use CASE...WHEN and GROUP BY to archive your expected output. I think it's more readable than using PIVOT function. Furthermore, it's T-SQL and be easier to migrate to other DBMS.

SELECT 
   ID, 
   BuildType,
   MAX(CASE FK_StageID WHEN 115 THEN DateStageChanged ELSE NULL END) AS [Land Purchased],
   MAX(CASE FK_StageID WHEN 116 THEN DateStageChanged ELSE NULL END) AS [Foundations],
   MAX(CASE FK_StageID WHEN 117 THEN DateStageChanged ELSE NULL END) AS [Timber Kit Erected / Wall Plate Level],
   MAX(CASE FK_StageID WHEN 118 THEN DateStageChanged ELSE NULL END) AS [Wind & Water Tight],
   MAX(CASE FK_StageID WHEN 119 THEN DateStageChanged ELSE NULL END) AS [1st Fix & Plastering],
   MAX(CASE FK_StageID WHEN 120 THEN DateStageChanged ELSE NULL END) AS [Final Fit Out],
   MAX(CASE FK_StageID WHEN 121 THEN DateStageChanged ELSE NULL END) AS [Completed],
   MAX(CASE FK_StageID WHEN 122 THEN DateStageChanged ELSE NULL END) AS [Redeemed]
FROM
   (
    -- original query  
        SELECT 
            s.ProjectStage, su.DateStageChanged, p.FK_ID As ID,
            s.LongDesc AS BuildType, su.FK_StageID  
        FROM 
            [dbo].[tbl_StageUpdates] AS su
        LEFT JOIN
            [dbo].[tbl_Stage] AS s ON su.FK_StageID = s.id
        LEFT JOIN 
            tbl_Projects AS p ON su.FK_ProjectID = p.PK_ProjectID
        WHERE 
            s.LongDesc = 'New Build'
        GROUP BY 
            p.FK_ID, s.ProjectStage, su.DateStageChanged, s.LongDesc, su.FK_StageID
    -- original query           
   )  Data 
GROUP BY 
   ID, BuildType

edited Jun 26 '17 at 12:38

answered Jun 26 '17 at 09:20

Trung Duong

3,475
2
8
9

Hi Trung, Thanks for taking the time to help me. I have used your suggested code however non of the ProtectStages are populated with dates i only get NULL values.The layout is perfect not sure why the dates are not being populated. – RustyHamster Jun 26 '17 at 11:38
@RustyHamster What is data type of ProjectStage column? Is it number data type or character data type? – Trung Duong Jun 26 '17 at 11:43
Hi Trung it is a INT data type – RustyHamster Jun 26 '17 at 11:50
Could you try to update CASE WHEN statement compare to INT data type? – Trung Duong Jun 26 '17 at 11:53
Not sure i understand what you want me to try. Am i to convert the Projectstage from int to date? – RustyHamster Jun 26 '17 at 12:00
No, INT data type, not DATE. Example, instead compare to '115', we will compare to 115 (integer value). I've updated my answer, please check. – Trung Duong Jun 26 '17 at 12:06
Hi Trung Im getting an error now Conversion failed when converting the varchar value 'Land Purchased' to data type int. – RustyHamster Jun 26 '17 at 12:12
Yes i have removed the quotes from the stages. Here is the error im getting . Conversion failed when converting the varchar value '1st Fix & Plastering' to data type int. – RustyHamster Jun 26 '17 at 12:26
I've got it. Sorry I've confused between stage ID and stage name fields. Is FK_StageID stored stage id? I've updated the answer, please check. – Trung Duong Jun 26 '17 at 12:41
Yes you were right Your query works perfectly thank you so much for helping me with this much appreciated. – RustyHamster Jun 26 '17 at 12:48

SQL Server 2012 Pivot Table

1 Answers1