CODEWITHSUNDEEP
sqlsql-serverpivotcross-apply
Luca Sagoleo
Luca Sagoleo

Reputation: 83

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!

Upvotes: 1

Views: 47

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

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

Upvotes: 1

Related Questions