SQL Foo | Multi-Column Pivot & Unpivot

Multi-Column Pivot & Unpivot with SQL

Multi-Column Pivot & Unpivot with SQL

Just for fun: Let’s look at some interesting ways to solve a multi-column pivot or unpivot (aka crosstab). Some methods are not ANSI standard and others simply won’t work on certain RDBMSs, but we’ll play around with some methods that I came up with using SQL Server 2016.

Let’s start by creating us some data!

The data used in this demo was generated using Mockaroo.com
Thanks, Mockaroo!

You will notice that the table we just created, populated with mock data, has several denormalized columns from a poorly architected attempt to create a daily aggregate from, what appears to be, weekly data. The initial developer decided it was easier to create a column for each day of the week for the sales amount and sales quantity. It’s our job now to unpivot that data back into rows so that we can do proper roll-ups and analysis.

Multi-Column unpivot using UNPIVOT clause:

This is the best, modern way to properly pivot and unpivot data in SQL. This example is a multi-column pivot technique that can pivot or unpivot data for more than one column at a time. The trick here is the use of the WHERE clause to “join” the data back together again. If you just want to manipulate a single column of data then you don’t need the where clause at all.

Multi-Column unpivot using CROSS APPLY clause with VALUES:

This is another great technique to pivot or unpviot data. By using VALUES within a CROSS APPLY, you can achieve a multi-column pivot by simply adding more values that you wish to pivot. This, however, is only available in some modern RDBMSs.

Multi-Column unpivot using UNION ALL:

This method, while carrying a heavy performance hit, is the defacto method to pivot & unpivot data. Older versions of RDBMSs all support this method and could be considered ANSI standard, although understand that it’s the use of UNION ALL that makes it that way.

There are other methods that are specific to your database but they’re not worth mentioning here due to the lack of generic usability. Everything you’ve seen in this post will work with any modern RDBMS and in one case will work with even the oldest production systems.

Can you come up with any other modern, yet generic techniques?