INTERNET APPLICATION DEVELOPMENT
MID MARKET ERP DEVELOPMENT
by Emma Tsai
In my last blog, I talked about how to use PIVOT operator to group and aggregate data in SQL database. Today, I am going to share how to Un-pivot data.
The starting point is some pivoted data. I used a sample table called Sales.FreightTotals as an example. The sample data shows as below:
As we can see, the source table has a row for each customer and a column for each shipper. The intersection of a customer and shipper is the total freight values. The Un-pivoting task is to return a row for each customer, the shipper ID in a second column, and the freight value in a third column.
In every un-pivoting task, we need to identify the three elements involved:
After identifying the three elements, you can use this query to un-pivot data.
In addition to un-pivoting the data, the UNPIVOT operator filters out rows with NULLs in the value column (freight).
In summary, PIVOT rotates data from a state of rows to a state of columns; While UNPIVOT rotates the data from columns to rows.
Reference: Exam 70-461 Querying Microsoft SQL Server 2012 by Itzik Ben-Gan, Dejan Sarka, and Ron Talmage