Flatten and group a set of data using PIVOT twice-CodePudding

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!

CodePudding user response：

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