SQL Server 2012 Pivot Table
I need to create a query that shows the results in a pivot table.
I have a spreadsheet that is regularly updated with clients. Build state changes.
We have 8 assembly steps
- 115 Acquired land
- 116 Foundations
- 117 Mounted / Wall Mounted Tile Timber Kit
- 118 Wind and Water Tight
- 119 1st patch and plaster
- 120 Final Fit Out
- 121 Completed
- 122 Extinguished
Here is my request
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 client ID that I would like the request to display the following information:
Can this be done?
Any help on this would be greatly appreciated
Greetings
+3
source to share
1 answer
If you have only 8 steps, you can use CASE ... WHEN and GROUP BY to archive the expected result. I think this is more readable than using the PIVOT function. Also, it is T-SQL and will be easier to port to other DBMSs.
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
+2
source to share