sqlsql-serverpivotcross-apply

Flatten and group a set of data using PIVOT twice


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.

Original data

This is the data I would like to show Desired data

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!


Solution

  • 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