Spotlight on UNPIVOT, Part 1

There have been thousands of articles and blog posts written about the PIVOT operator. PIVOT seems to get all the glory. Meanwhile, UNPIVOT doesn’t really get equal time… it’s like the poor stepchild that everyone knows is there but is ignored.

In this article I’m going to give poor UNPIVOT the spotlight.

We’ll take a look at how it works, and, as a result, we’ll end up discovering a new and fresh approach to unpivoting multiple sets of columns.

Next time, in Part 2, I’ll go over some unpivoting examples that (gasp) might even be practical.

An Introduction to UNPIVOT

Very simply put, the UNPIVOT operator will convert columns into rows. (Conversely, the PIVOT operator will aggregate rows into columns… and that’s all we’ll say about PIVOT for the rest of this article… it’s UNPIVOT’s time to shine).

Let’s look at a quick example. Let’s say that it’s July 2009 and we have a table containing quarterly sales data by year. Note that we NULL values for the 3rd and 4th Quarter of 2009 because they haven’t occurred yet:

if object_id('tempdb..#QSales','U') is not null drop table #QSales
go
create table #QSales
(
   [Year] int
  ,Q1Sales int
  ,Q2Sales int
  ,Q3Sales int
  ,Q4Sales int
)
go
insert #QSales select 2007, 737968, 821029, 792150, 830155
     union all select 2008, 801632, 837129, 809461, 841883
     union all select 2009, 839260, 843654,   null,   null
 
select * from #QSales
/*
Year 1Sales Q2Sales Q3Sales Q4Sales
---- ------ ------- ------- -------
2007 737968  821029  792150  830155
2008 801632  837129  809461  841883
2009 839260  843654    NULL    NULL
*/

We can use UNPIVOT in order to “normalize” these columns into rows of data:

select *
from #QSales
unpivot (Sales for Descript in (Q1Sales,Q2Sales,Q3Sales,Q4Sales)) P
/*
Year  Sales Descript
---- ------ --------
2007 737968 Q1Sales
2007 821029 Q2Sales
2007 792150 Q3Sales
2007 830155 Q4Sales
2008 801632 Q1Sales
2008 837129 Q2Sales
2008 809461 Q3Sales
2008 841883 Q4Sales
2009 839260 Q1Sales
2009 843654 Q2Sales
*/

So, for each row in our original table, we have converted/expanded the Q1Sales, Q2Sales, Q3Sales, and Q4Sales columns into 1 row each, creating two new column names: Sales (which contains the value of our original columns) and Descript (which contains the name of our original columns). This is why, in this example, the Descript column is created as a NVARCHAR(128)… because 128 is the maximum length for the name of a column.

You can see that UNPIVOT eliminated the NULL values automatically, which is fine in this example. UNPIVOT always does this, and there’s nothing we can do to change that behavior… but there may be cases (which I will give an example of later) where we do not want this to happen.

Anyway, our result set with the Descript column is not necessarily that helpful… It might be more appropriate to actually have an INTEGER column representing the actual Quarter. In order to accomplish this, we’ll have to pull that quarter value out of our Descript column:

select [Year]
      ,Qtr=cast(substring(Descript,2,1) as int)
      ,Sales
from #QSales
unpivot (Sales for Descript in (Q1Sales,Q2Sales,Q3Sales,Q4Sales)) P
/*
Year Qtr  Sales
---- --- ------
2007   1 737968
2007   2 821029
2007   3 792150
2007   4 830155
2008   1 801632
2008   2 837129
2008   3 809461
2008   4 841883
2009   1 839260
2009   2 843654
*/

Looking Under The Hood

So what does an UNPIVOT query plan look like? Here is the plan of the first UNPIVOT query we did in this article (the one with the SELECT * in it):



At first I was surprised to see a LEFT JOIN, but in looking at the properties of the Nested Loops operator, there was no JOIN condition indicated anywhere:



And in the Constant Scan properties, I saw this:



It was creating an output list of 2 columns, consisting of these 4 row values:

values (N'Q1Sales', Q1Sales)
      ,(N'Q2Sales', Q2Sales)
      ,(N'Q3Sales', Q3Sales)
      ,(N'Q4Sales', Q4Sales)

So this was not a traditional simple LEFT JOIN, it was a correlated LEFT JOIN, which is really my old friend, the APPLY operator.

This was a revelation! The UNPIVOT is nothing but an OUTER APPLY operation with a filter operator added to the plan to get rid of the NULL values. The following two queries are (for all intents and purposes) exactly the same (they have a 50%:50% relative cost):

select [Year],Sales,Descript
from #QSales
unpivot (Sales for Descript in (Q1Sales,Q2Sales,Q3Sales,Q4Sales)) P
 
select [Year],Sales,Descript
from #QSales
outer apply (select N'Q1Sales',Q1Sales union all
             select N'Q2Sales',Q2Sales union all
             select N'Q3Sales',Q3Sales union all
             select N'Q4Sales',Q4Sales) P(Descript,Sales)
where Sales is not null             
 

And using SQL2008’s table-value constructors, the APPLY query looks even simpler:

select [Year],Sales,Descript
from #QSales
outer apply (values (N'Q1Sales',Q1Sales)
                   ,(N'Q2Sales',Q2Sales)
                   ,(N'Q3Sales',Q3Sales)
                   ,(N'Q4Sales',Q4Sales)) P(Descript,Sales)
where Sales is not null             
 

I don’t really know why the Optimizer chose to use an OUTER APPLY rather than a CROSS APPLY. After all, it’s impossible for the Constant Scan to produce any empty set, so there doesn’t seem to be any reason to employ an OUTER APPLY. So the same query can be safely written using CROSS APPLY.

One might argue that the UNPIVOT query takes less typing and appears much simpler, but we have so much more freedom with our output using the APPLY approach.

For example, remember our second UNPIVOT query above, where we wanted the result set with an integer Quarter value, and we had to pull out that value from the Descript column? Well, we can simply throw our Quarter value into the APPLY derived table… AND, even better, if we decide that we do want to include the NULL values, we have the freedom to do that… we just don’t bother with the WHERE clause:

select [Year],Qtr,Sales
from #QSales
cross apply (values (1,Q1Sales)
                   ,(2,Q2Sales)
                   ,(3,Q3Sales)
                   ,(4,Q4Sales)) P(Qtr,Sales)
/*
Year Qtr  Sales
---- --- ------
2007   1 737968
2007   2 821029
2007   3 792150
2007   4 830155
2008   1 801632
2008   2 837129
2008   3 809461
2008   4 841883
2009   1 839260
2009   2 843654
2009   3   NULL
2009   4   NULL
*/

You know, something just occurred to me…

Here I am writing a blog post about UNPIVOT, giving it some time in the spotlight, and then I end up slapping it in the face and turning my back on it and showing you a better, more flexible way to accomplish the same thing.

Poor UNPIVOT.

Oh well, c’est la vie.

Unpivoting Multiple Sets of Columns

As of SQL2008, the UNPIVOT operator does not have the capability to unpivot multiple sets of columns of data. For example, let’s say that we had a table of Blood Test information, recording Cholesterol, Sodium, and Glucose levels in various patients over up to 3 different dates:

if object_id('tempdb..#BloodTests','U') is not null drop table #BloodTests
go
create table #BloodTests
(
   PatientID int primary key
  ,TestDate1 date
  ,Chol1 int
  ,Sod1 int
  ,Gluc1 int
  ,TestDate2 date
  ,Chol2 int
  ,Sod2 int
  ,Gluc2 int
  ,TestDate3 date
  ,Chol3 int
  ,Sod3 int
  ,Gluc3 int
)
go
insert #BloodTests 
select 1,'20091101', 180, 136,  90
        ,'20091201', 190, 137,  89
        ,'20100101', 195, 139,  92
union all
select 2,'20091115', 200, 140, 100
        ,'20091223', 205, 141, 105
        ,null      ,null,null,null
union all
select 3,'20091216', 175, 135,null
        ,'20100103', 177, 141,  71
        ,'20100131', 179, 143,  73
 
select * from #BloodTests
/*
PatientID  TestDate1  Chol1 Sod1 Gluc1  TestDate2  Chol2 Sod2 Gluc2  TestDate3  Chol3 Sod3 Gluc3
---------  ---------- ----- ---- -----  ---------- ----- ---- -----  ---------- ----- ---- -----
        1  2009-11-01   180  136    90  2009-12-01   190  137    89  2010-01-01   195  139    92
        2  2009-11-15   200  140   100  2009-12-23   205  141   105  NULL        NULL NULL  NULL
        3  2009-12-16   175  135  NULL  2010-01-03   177  141    71  2010-01-31   179  143    73
*/

Note that Patient Number 2 only had two tests performed… the values for his 3rd set of values are all set to NULL. And note that Patient Number 3 did not have a Glucose Test performed in his test of Dec16,2009 (because a Glucose test requires fasting, and Patient Number 3 forgot and scarfed down a half-dozen waffles for breakfast that morning).

Yes, it’s true, this is a lousy way to record data, with all these columns of information, and therefore, we will unpivot that information into a nice normalized result set.

The traditional way to unpivot multiple sets of data… the method used in SQL2000 before any UNPIVOT operator was introduced… was as follows:

select PatientID
      ,TestDate=case TestNo
                  when 1 then TestDate1
                  when 2 then TestDate2
                  when 3 then TestDate3
                end
      ,Cholesterol=case TestNo
                     when 1 then Chol1
                     when 2 then Chol2
                     when 3 then Chol3
                   end
      ,Sodium=case TestNo
                when 1 then Sod1
                when 2 then Sod2
                when 3 then Sod3
              end
      ,Glucose=case TestNo
                 when 1 then Gluc1
                 when 2 then Gluc2
                 when 3 then Gluc3
               end
from #BloodTests
cross join (select 1 union all
            select 2 union all
            select 3) X(TestNo)
/*
PatientID TestDate   Cholesterol Sodium Glucose
--------- ---------- ----------- ------ -------
        1 2009-11-01         180    136      90
        1 2009-12-01         190    137      89
        1 2010-01-01         195    139      92
        2 2009-11-15         200    140     100
        2 2009-12-23         205    141     105
        2 NULL              NULL   NULL    NULL
        3 2009-12-16         175    135    NULL
        3 2010-01-03         177    141      71
        3 2010-01-31         179    143      73
*/

This approach does a CROSS JOIN with the table and a derived table of integers (representing the column number groups) and then CASE expressions are used to output the appropriate values in the SELECT list.

Note that Patient Number 2’s “empty” test was included here. If we wanted to eliminate NULLs, we would have to add a WHERE clause, but it wouldn’t be that straightforward. We would either have to duplicate the CASE expression in a WHERE clause like so (ewww… yuck):

where case TestNo
        when 1 then TestDate1
        when 2 then TestDate2
        when 3 then TestDate3
      end is not null

Or we would have to put the whole query into a CTE or a derived table and apply a WHERE to the TestDate column like so:

select PatientID,TestDate,Cholesterol,Sodium,Glucose
from (select PatientID
            ,TestDate=case TestNo
                        when 1 then TestDate1
                        when 2 then TestDate2
                        when 3 then TestDate3
                      end
            ,Cholesterol=case TestNo
                           when 1 then Chol1
                           when 2 then Chol2
                           when 3 then Chol3
                         end
            ,Sodium=case TestNo
                      when 1 then Sod1
                      when 2 then Sod2
                      when 3 then Sod3
                    end
            ,Glucose=case TestNo
                       when 1 then Gluc1
                       when 2 then Gluc2
                       when 3 then Gluc3
                     end
      from #BloodTests
      cross join (select 1 union all
                  select 2 union all
                  select 3) X(TestNo)
      ) UnPvt
where TestDate is not null
/*
PatientID TestDate   Cholesterol Sodium Glucose
--------- ---------- ----------- ------ -------
        1 2009-11-01         180    136      90
        1 2009-12-01         190    137      89
        1 2010-01-01         195    139      92
        2 2009-11-15         200    140     100
        2 2009-12-23         205    141     105
        3 2009-12-16         175    135    NULL
        3 2010-01-03         177    141      71
        3 2010-01-31         179    143      73
*/

Either way, this CROSS JOIN approach is pretty cumbersome to maintain and is not very easy to understand at first glance. (And, pardon my political incorrectness, but it’s ugly as hell too).

Another clever way to approach a multiple unpivot was to actually employ the UNPIVOT operator multiple times. I’ll show you this in steps. First, let’s UNPIVOT the TestDate columns:

select *
from #BloodTests
unpivot (TestDate for DateDesc in (TestDate1,TestDate2,TestDate3)) U1
/*
PatientID Chol1 Sod1 Gluc1 Chol2 Sod2 Gluc2 Chol3 Sod3 Gluc3 TestDate   DateDesc
--------- ----- ---- ----- ----- ---- ----- ----- ---- ----- ---------- ---------
        1   180  136    90   190  137    89   195  139    92 2009-11-01 TestDate1
        1   180  136    90   190  137    89   195  139    92 2009-12-01 TestDate2
        1   180  136    90   190  137    89   195  139    92 2010-01-01 TestDate3
        2   200  140   100   205  141   105  NULL NULL  NULL 2009-11-15 TestDate1
        2   200  140   100   205  141   105  NULL NULL  NULL 2009-12-23 TestDate2
        3   175  135  NULL   177  141    71   179  143    73 2009-12-16 TestDate1
        3   175  135  NULL   177  141    71   179  143    73 2010-01-03 TestDate2
        3   175  135  NULL   177  141    71   179  143    73 2010-01-31 TestDate3
*/

That expanded our table of 3 rows into 8 rows (the TestDate3 value was eliminated for Patient Number 2 because it was NULL).

Now we can perform a second UNPIVOT, this time on the Cholesterol columns:

select *
from #BloodTests
unpivot (TestDate for DateDesc in (TestDate1,TestDate2,TestDate3)) U1
unpivot (Cholesterol for CholDesc in (Chol1,Chol2,Chol3)) U2
/*
PatientID Sod1 Gluc1 Sod2 Gluc2 Sod3 Gluc3 TestDate   DateDesc  Cholesterol CholDesc
--------- ---- ----- ---- ----- ---- ----- ---------- --------- ----------- --------
        1  136    90  137    89  139    92 2009-11-01 TestDate1         180 Chol1
        1  136    90  137    89  139    92 2009-11-01 TestDate1         190 Chol2
        1  136    90  137    89  139    92 2009-11-01 TestDate1         195 Chol3
        1  136    90  137    89  139    92 2009-12-01 TestDate2         180 Chol1
        1  136    90  137    89  139    92 2009-12-01 TestDate2         190 Chol2
        1  136    90  137    89  139    92 2009-12-01 TestDate2         195 Chol3
        1  136    90  137    89  139    92 2010-01-01 TestDate3         180 Chol1
        1  136    90  137    89  139    92 2010-01-01 TestDate3         190 Chol2
        1  136    90  137    89  139    92 2010-01-01 TestDate3         195 Chol3
        2  140   100  141   105 NULL  NULL 2009-11-15 TestDate1         200 Chol1
        2  140   100  141   105 NULL  NULL 2009-11-15 TestDate1         205 Chol2
        2  140   100  141   105 NULL  NULL 2009-12-23 TestDate2         200 Chol1
        2  140   100  141   105 NULL  NULL 2009-12-23 TestDate2         205 Chol2
        3  135  NULL  141    71  143    73 2009-12-16 TestDate1         175 Chol1
        3  135  NULL  141    71  143    73 2009-12-16 TestDate1         177 Chol2
        3  135  NULL  141    71  143    73 2009-12-16 TestDate1         179 Chol3
        3  135  NULL  141    71  143    73 2010-01-03 TestDate2         175 Chol1
        3  135  NULL  141    71  143    73 2010-01-03 TestDate2         177 Chol2
        3  135  NULL  141    71  143    73 2010-01-03 TestDate2         179 Chol3
        3  135  NULL  141    71  143    73 2010-01-31 TestDate3         175 Chol1
        3  135  NULL  141    71  143    73 2010-01-31 TestDate3         177 Chol2
        3  135  NULL  141    71  143    73 2010-01-31 TestDate3         179 Chol3
*/

Now we’ve ended up expanding that to 22 rows of data. If we add two more UNPIVOTS to take care of the Sodium and Glucose columns, here’s what we end up with:

select *
from #BloodTests
unpivot (TestDate for DateDesc in (TestDate1,TestDate2,TestDate3)) U1
unpivot (Cholesterol for CholDesc in (Chol1,Chol2,Chol3)) U2
unpivot (Sodium for SodDesc in (Sod1,Sod2,Sod3)) U3
unpivot (Glucose for GlucDesc in (Gluc1,Gluc2,Gluc3)) U4
/*
PatientID TestDate   DateDesc  Cholesterol CholDesc Sodium SodDesc Glucose GlucDesc
--------- ---------- --------- ----------- -------- ------ ------- ------- --------
        1 2009-11-01 TestDate1         180 Chol1       136 Sod1         90 Gluc1
        1 2009-11-01 TestDate1         180 Chol1       136 Sod1         89 Gluc2
        1 2009-11-01 TestDate1         180 Chol1       136 Sod1         92 Gluc3
        1 2009-11-01 TestDate1         180 Chol1       137 Sod2         90 Gluc1
        1 2009-11-01 TestDate1         180 Chol1       137 Sod2         89 Gluc2
        1 2009-11-01 TestDate1         180 Chol1       137 Sod2         92 Gluc3
        1 2009-11-01 TestDate1         180 Chol1       139 Sod3         90 Gluc1
        1 2009-11-01 TestDate1         180 Chol1       139 Sod3         89 Gluc2
        1 2009-11-01 TestDate1         180 Chol1       139 Sod3         92 Gluc3
        1 2009-11-01 TestDate1         190 Chol2       136 Sod1         90 Gluc1
        1 2009-11-01 TestDate1         190 Chol2       136 Sod1         89 Gluc2
        1 2009-11-01 TestDate1         190 Chol2       136 Sod1         92 Gluc3
        ...
        3 2010-01-31 TestDate3         177 Chol2       135 Sod1         71 Gluc2
        3 2010-01-31 TestDate3         177 Chol2       135 Sod1         73 Gluc3
        3 2010-01-31 TestDate3         177 Chol2       141 Sod2         71 Gluc2
        3 2010-01-31 TestDate3         177 Chol2       141 Sod2         73 Gluc3
        3 2010-01-31 TestDate3         177 Chol2       143 Sod3         71 Gluc2
        3 2010-01-31 TestDate3         177 Chol2       143 Sod3         73 Gluc3
        3 2010-01-31 TestDate3         179 Chol3       135 Sod1         71 Gluc2
        3 2010-01-31 TestDate3         179 Chol3       135 Sod1         73 Gluc3
        3 2010-01-31 TestDate3         179 Chol3       141 Sod2         71 Gluc2
        3 2010-01-31 TestDate3         179 Chol3       141 Sod2         73 Gluc3
        3 2010-01-31 TestDate3         179 Chol3       143 Sod3         71 Gluc2
        3 2010-01-31 TestDate3         179 Chol3       143 Sod3         73 Gluc3
(151 Rows Total)
*/

So that’s now grown to 151 rows. Obviously this is not what we want… we have to filter out the “noise” and just get the rows that have a common column index… in other words, the rows whose Description columns have the right-most character in common… We want the rows where TestDate1 and Chol1 and Sod1 and Gluc1 are together, and the same for the ones that end with ‘2’ and ‘3’. We just do that in the WHERE clause (and we’ll also eliminate those pesky Description columns in the SELECT list to come up with the following):

select PatientID,TestDate,Cholesterol,Sodium,Glucose
from #BloodTests
unpivot (TestDate for DateDesc in (TestDate1,TestDate2,TestDate3)) U1
unpivot (Cholesterol for CholDesc in (Chol1,Chol2,Chol3)) U2
unpivot (Sodium for SodDesc in (Sod1,Sod2,Sod3)) U3
unpivot (Glucose for GlucDesc in (Gluc1,Gluc2,Gluc3)) U4
where right(DateDesc,1)=right(CholDesc,1)
  and right(CholDesc,1)=right(SodDesc,1)
  and right(SodDesc,1)=right(GlucDesc,1)
/*
PatientID TestDate   Cholesterol Sodium Glucose
--------- ---------- ----------- ------ -------
        1 2009-11-01         180    136      90
        1 2009-12-01         190    137      89
        1 2010-01-01         195    139      92
        2 2009-11-15         200    140     100
        2 2009-12-23         205    141     105
        3 2010-01-03         177    141      71
        3 2010-01-31         179    143      73
*/

Isn’t that clever? It’s so much more compact than the CROSS JOIN method and it…

Wait a minute…

Do you notice a row that’s missing? What happened to our Patient Number 3’s test from Dec16,2009? It’s gone!

Yep… remember that UNPIVOT has that nasty habit of eliminating NULL values. So when we UNPIVOTted the Glucose columns, Patient Number 3’s test with the NULL Glucose value got thrown out the window, and we ended up losing ALL of his Dec16,2009 data.

This is, unfortunately, a side-effect of using this method.

But hey… now that we have the knowledge that we can unpivot columns with an APPLY operator, unpivoting multiple columns of data has now become laughably easier. Take a look:

select PatientID,TestDate,Cholesterol,Sodium,Glucose
from #BloodTests
cross apply (select TestDate1,Chol1,Sod1,Gluc1 union all
             select TestDate2,Chol2,Sod2,Gluc2 union all
             select TestDate3,Chol3,Sod3,Gluc3) X(TestDate,Cholesterol,Sodium,Glucose)
/*
PatientID TestDate   Cholesterol Sodium Glucose
--------- ---------- ----------- ------ -------
        1 2009-11-01         180    136      90
        1 2009-12-01         190    137      89
        1 2010-01-01         195    139      92
        2 2009-11-15         200    140     100
        2 2009-12-23         205    141     105
        2 NULL              NULL   NULL    NULL
        3 2009-12-16         175    135    NULL
        3 2010-01-03         177    141      71
        3 2010-01-31         179    143      73
*/

Beautiful, isn’t it?

Oops… I forgot to eliminate the NULL that we really want to eliminate… Patient Number 2’s “empty” test. So we’ll add a WHERE clause, and, just for kicks, employ SQL2008’s table-value constructor syntax to make it look even cleaner:

select PatientID,TestDate,Cholesterol,Sodium,Glucose
from #BloodTests
cross apply (values (TestDate1,Chol1,Sod1,Gluc1)
                   ,(TestDate2,Chol2,Sod2,Gluc2)
                   ,(TestDate3,Chol3,Sod3,Gluc3)) X(TestDate,Cholesterol,Sodium,Glucose)
where TestDate is not null
/*
PatientID TestDate   Cholesterol Sodium Glucose
--------- ---------- ----------- ------ -------
        1 2009-11-01         180    136      90
        1 2009-12-01         190    137      89
        1 2010-01-01         195    139      92
        2 2009-11-15         200    140     100
        2 2009-12-23         205    141     105
        3 2009-12-16         175    135    NULL
        3 2010-01-03         177    141      71
        3 2010-01-31         179    143      73
*/

Isn’t that terrific? So simple… So clean… So compact… So elegant.

I don’t think MSFT needs to introduce an UNPIVOT that handles multiple sets of data. Why bother when you have an approach as simple as this?

In my next blog entry, I’ll go over some additional examples… some fun and some practical… of unpivoting data.