I'm trying to flatten a set of data from a SQL query (using MS SQL Server) and I need to do it twice.
This is the example data I have.
This is the data I would like to show
I managed to get one height and one area for each building using PIVOT but what I need is to pivot again in order to have one row for every building that contains all the related data.
I think the solution requires the use of both PIVOT and CROSS APPLY but I cannot find the right way to use them.
Thanks for your help!
No need for a CROSS APPLY, a simple PIVOT or Conditional Aggregation would do. Just remember to "FEED" your pivot with the minimum number of required columns.
Select *
From (
Select Building
,Item = concat([Floor],MeasureName)
,Value = MeasureValue
From YourTable
) src
Pivot ( max( Value ) for Item in ( [floor1height]
,[floor1area]
,[floor2height]
,[floor2area]
) ) Pvt