Everyone seems to want to “pivot” or “cross tab” data, but knowing how to do the opposite is equally important.  In fact, I would argue that the skill of “unpivoting” data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set.  We all know that there’s lots of bad databases designs out there, so this can be a handy technique to know. 

Of course, even a well designed, fully normalized database can still benefit from “unpivoting” from time to time, so let’s take a look at some common situations and some of the options we have to handle this at our disposal.  We will focus on some traditional SQL techniques to do this, and then take a close look at the UNPIVOT operator that was introduced with SQL Server 2005.

Example #1:  A Bad database design

Let’s start with a commonly bad table design, in which someone has decided to relate a client to multiple contacts by designing their client table like this:

(Note: For brevity, I am not including the contact table here, nor the foreign key constraints.  Of course, with this table design, it would probably be pretty unlikely to find such constraints in the database anyway)


With this design, it is not very easy or efficient to get a count of all contacts for each client, or to find out which contacts are related to which clients.   One thing we can do, however is to “unpivot” this table in a query that returns 1 row per ClientID/ContactID combination.  With that result set, we can easily now reference the table as if it were normalized and we can get the information we need.

One way to do is to use UNION ALL to return each row in the clients table 4 times, and each time return a different contactID column:

Another option is to CROSS JOIN the Clients table with a table or resultset that returns 4 rows, which also effectively returns each row in the clients table 4 times.  For each of the 4 values in the table we are cross joining, we grab a different contact column:

(Note that you can use a permanent table of Numbers in your database instead of generating it on the fly with a UNION, as shown)

Finally, however, there is an even eaiser way to handle this: the UNPIVOT operator, new with SQL 2005.  UNPIVOT works very efficiently and really allows you to handle this exact situation quite easily:

Much shorter to write, and more efficient to execute as well. 

Taking a Closer Look at UNPIVOT

The UNPIVOT operator is tricky to get a feel for, however, so let’s take a look at it.

First, the “As Contact” at the end is just labeling the entire unpivot result set with an alias, just as you must alias a derived table.  Each column returned by the pivot operator can be referenced by the alias if necessary.

The “ContactID for” part says that we want to return a column called “ContactID” for each unpivoted row.  The IN() list is the columns that we are unpivoting; the values in the 4 columns listed here will be assigned to the ContactID column in the result.  So, the first time a particular row is unpivoted, the value of the ‘contact1′ column is assigned to ContactID, the next time it is the ‘contact2′ column, then ‘contact3′, and then finally ‘contact4′.  Then, the next row is processed and it all begins again. 

Thus, because we are unpivoting 4 values, we know that the result of the unpivot will have 4 times as many rows as the source data.

UNPIVOT returns an additional column as well, which contains the name of the column that was used to produce each unpivoted row.   Here, we have specified that to be called ContactNumber.  Note that we actually did not return ContactNumber in our example, be we can easily add that in so you can see how it works:

So, you can see that the code to write is very short, but a little difficult to grasp at first.  In the end, though, we are able to take a bad table design and easily “fix it”, at least temporarily, so that we can query it using simple and standard SQL statements to get what we need.

Example #2:  Normalizing a Transaction Table

Here’s another common example:

Notice that we have different columns for Budget, Actual and Projection, which is not really a great database design.  Much better would be to break this data out so that we have a single ‘Amount’ column and a ‘TransactionType’ column that specifies the type of each transaction.  We can transform our Transactions tables into this format using UNION ALL:

Or, we can use the UNPIVOT operator to do the same much easier:

Example #3: “Unsummarizing” Data

For those who work with accounting systems, this example may be familiar to you.   Many times, Accounting systems have “summary” tables that roll up transactional data into a structure like this:

(As before, let’s not worry about those foreign key constraints)

These tables are often calculated when transactions are posted, or periods are closed.   Typically, many reports pull from these tables because it is much more efficient than summarizing thousands or millions of transactions, and the data is already “cross-tabbed” the way most reporting tools would like to display it.

We can take this summarized data and “unpivot” it so we can still access the summarized data, but now it will be in a normalized structure.  All it takes is a simple UNPIVOT like this:

We can filter so that only periods with a non-zero amount are included, and we can SELECT FROM this result set and get the exact data we need for whatever date range we want without worrying which column the actual data is in.

This can also be done with a CROSS JOIN or a UNION ALL, but with 12 values to pivot, those options become much longer to write and UNPIVOT appears to be the way to go in this case.

Example #4: Multiple unpivots

Finally, let’s consider a more complicated example.  Here, we have a table that stores games played between two teams, where one team is the HomeTeam and the other is the AwayTeam:

That may or may not be the best possible design for the Games table, but it is a common way to do it.  With data in that form, if we want to get the total runs scored per team across all games, or get each teams won-loss record, we need to make two passes through the table. This can be done fairly easily and efficiently with a union:

Now, how can we do this with UNPIVOT?  This example is a bit more complicated, because we are “unpivoting” not only the score, but also the TeamCode, and we are also calculating a few extra columns (Win, Loss and Tie).

So, can we UNPIVOT more than 1 column?  Let’s start simply and do things one at a time.  First, let’s UNPIVOT just the TeamCode:

OK, so far so good.  Now, how do we get the score for the team as well?  Let’s add another UNPIVOT clause to the SELECT, this time for Score:

Hmmm. OK, we cannot specify the same column for both pivots.  That is just an alias specification, no big deal, so let’s just alias it as “HomeOrAway2″ :

Holy schnikies, it works!  We can specify more than one UNPIVOT clause for the same SQL statement!  Who would have thunk it?

Uh oh — wait a second.  We have 24 results returned.  We should have only 12.  Something is not right here.  Remember when we aliased our second unpivot column as “HomeOrAway2″?  We did not return that anywhere in our results.  Let’s add that in and take a look:

April 23rd, 2008 | Posted in Miscellaneous | No Comments