CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jared
jared

Reputation: 483

Keep max date in column when pivoting

I have this data that i want to pivot. It all goes well as long as i don't include the datewhat differs on some rows. I would like to keep the highest date for each set of idAnalyseItem. My query looks like this for the moment, and i struggle with how the date part of it fits in.

WITH Analyser AS (
    SELECT ElementItems.idAnalyseItem as Analyse, ESymbol as Symbol, EIValue as Verdi
    FROM ElementItems
)
SELECT * FROM Analyser

PIVOT(
    MAX(Verdi) FOR Symbol IN ([Justert],[Produkt Type],[Start Time],[Stopp Time],[Dunger],[Våtvekt],[Vann],[Fe-tot],[Fe-Mag],[Svovel],[P],[SiO2],[MnO],[Al2O3],[CaO],[1,19],[0,841],[0,425],[0,212],[0,125],[0,106],[0,075],[0,063],[0,045],[0,02])
) AS PivotTable 

ORDER BY Analyse DESC

This is my dataset. Microsoft SQL Server 2008.

My dataset (or at least some of it)

Upvotes: 0

Views: 239

Answers (1)

PSK
PSK

Reputation: 17943

You can change your query like following, to get the MAX(EIDateTime) for all the rows belongs to a idAnalyseItem

;WITH Analyser AS (
    SELECT ElementItems.idAnalyseItem as Analyse, ESymbol as Symbol, EIValue as Verdi, MAX(EIDateTime) OVER(PARTITION BY ElementItems.idAnalyseItem) EIDateTime 
    FROM ElementItems
)
SELECT * FROM Analyser
PIVOT(
    MAX(Verdi) FOR Symbol IN ([Justert],[Produkt Type],[Start Time],[Stopp Time],[Dunger],[Våtvekt],[Vann],[Fe-tot],[Fe-Mag],[Svovel],[P],[SiO2],[MnO],[Al2O3],[CaO],[1,19],[0,841],[0,425],[0,212],[0,125],[0,106],[0,075],[0,063],[0,045],[0,02])
) AS PivotTable 

ORDER BY Analyse DESC

Same thing can be done using GROUP BY like following.

;WITH Analyser AS (
    SELECT ElementItems.idAnalyseItem as Analyse, ESymbol as Symbol, EIValue as Verdi, MAX(EIDateTime) as EIDateTime 
    FROM ElementItems
    GROUP BY idAnalyseItem,ESymbol,EIValue
)
SELECT * FROM Analyser
PIVOT(
    MAX(Verdi) FOR Symbol IN ([Justert],[Produkt Type],[Start Time],[Stopp Time],[Dunger],[Våtvekt],[Vann],[Fe-tot],[Fe-Mag],[Svovel],[P],[SiO2],[MnO],[Al2O3],[CaO],[1,19],[0,841],[0,425],[0,212],[0,125],[0,106],[0,075],[0,063],[0,045],[0,02])
) AS PivotTable 

ORDER BY Analyse DESC

Upvotes: 1

Related Questions