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:

• Source columns that you are un-pivoting, in this case, [1], [2], and [3]
• The name you want to assign to the target values column in this case, freight
• The name you want to assign to the target names columns, in this case, shipperid

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