Monday, February 22, 2010

Spotlight on UNPIVOT, Part 1

Spotlight on UNPIVOTThere 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):

UNPIVOT Query Plan

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:

Nested Loops Properties

And in the Constant Scan properties, I saw this:

Constant Scan Properties

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.

18 comments:

  1. I'm guessing they put in PIVOT/UNPIVOT because people asked for it. The implementation is mostly convenience. Should more and more people use it, they might decide to expend more effort on it and add options outside of the original request.

    ReplyDelete
  2. Very nicely explained, Brad. Can't wait to see part 2.

    ReplyDelete
  3. Excellent Brad.

    PS: I was thinking where were you for a long time and today saw your blog:) You have wonderful stuff there buddy.

    Cetin Basoz

    ReplyDelete
  4. @Rob, @Syed: Thanks!

    @Brian: I agree... I'm sure that in a future version they might add a INCLUDE NULLS option to UNPIVOT, for example.

    ReplyDelete
  5. @Cetin!!!

    Great to hear from you! Glad you found me... Hope all is well.

    ReplyDelete
  6. @Brad

    Perhaps they're continuing the wonderful tradition of UNION to do something DISTINCT that noone asked for.

    Just another reason to love unions. They did such a good job with the employee-base they went to the data-base for the sequel.

    ReplyDelete
  7. Brad,

    I see you have been digging into the execution plan.

    Good work!!!

    AMB

    ReplyDelete
  8. Great post, I've learned several things from it already and as I keep studying it I'm sure I'll learn a few more. One of the things that I learned (that I've somehow never seen before) was renaming columns in an alias like you did above. I had no idea you could do that.

    ReplyDelete
  9. @Garadin:

    Thanks for the feedback! I'm glad that it's been a good learning experience... that's always great to hear.

    ReplyDelete
  10. Another approach with grouping set:

    select * from (
    select patientid,
    coalesce(testdate1, testdate2, testdate3) as testdate,
    coalesce(chol1,chol2,chol3) as chol,
    coalesce(sod1, sod2, sod3) as sod,
    coalesce(gluc1, gluc2, gluc3) as gluce
    from #BloodTests
    group by grouping sets (patientID),
    grouping sets ((testdate1, chol1, sod1, gluc1),
    (testdate2, chol2, sod2, gluc2),
    (testdate3, chol3, sod3, gluc3)))d
    where TestDate is not null
    order by patientid;

    ReplyDelete
  11. Hi Brad,
    What is your opinion about my solution in the previous post? Is it OK?
    Also be some other method like hybrid (native + traditional) and using just one UNPIVOT.

    ReplyDelete
  12. Hi Mohammad...

    The GROUPING SETS approach is clever, and it does produce the correct result.

    However, it ends up doing 3 SCANs of the table as opposed to just 1 SCAN with the CROSS APPLY method.

    The relative costs of the estimated execution plans were 18%:82% (CROSSAPPLY:GROUPINGSETS).

    I have to investigate GROUPING SETS a little more... That's one new aspect of SQL2008 that I haven't really sunk my teeth into.

    --Brad

    ReplyDelete
  13. Awesome , Brad.. excellent info.. As Garadin said, i learnt a LOT from this post.. Thanks sir..

    ReplyDelete
  14. arriving very late to the party, but I enjoyed this article so much I wanted to thank you anyway.

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. im too late, but i neee to ask.. i have a big trouble with a tables... some retard guy make this table..
    id year var jan feb ...... etc.
    1 2008 var1 123 NULL
    2 2008 var2 678 87

    and i rly need to change that schema to
    id year month var1 var2
    1 2008 01 123 678
    1 2008 02 NULL 87

    i tried with unpivot..((Select id,year, month, var1
    from (select id, year, jun, feb .. etc from X
    where var='var1') p
    UNPIVOT
    (var1 for mont in(jun, feb...etc)) as unpvt; )) but it was 51 selects and 51 temp tables to store the data in order(i have 51 var) and then a big select join in order to remake the table >.< and takes a rly big time to make that operation.. so any good ideas for my problem??.. btw sorry for my bad english

    ReplyDelete