October 7, 2013 at 1:12 pm
I have an unpivoted table that seems ordered the way I want but I am not sure if it is guarenteed to be that way.
If I have a table:
YearTotalNewTotalLostNetGain
201329544.48-10832.0018712.48
201235549.67-20252.6515297.02
2011136816.89-22860.54113956.35
201045795.48-54933.17-9137.69
200961113.35-19419.341694.05
And I want to unpivot it:
SELECT ActivityYear,Activities, Activity
FROM
(
SELECT BoundOrLostYear AS ActivityYear ,
Totalnew ,
TotalLost ,
NetGain
FROM @FinalTable2
) p
UNPIVOT(Activity FOR Activities IN (Totalnew, TotalLost, NetGain)) b
This comes out perfect.
YearActivitiesActivity
2013Totalnew29544.48
2013TotalLost-10832
2013NetGain18712.48
2012Totalnew35549.67
2012TotalLost-20252.65
2012NetGain15297.02
2011Totalnew136816.89
2011TotalLost-22860.54
2011NetGain113956.35
2010Totalnew45795.48
2010TotalLost-54933.17
2010NetGain-9137.69
2009Totalnew61113.35
2009TotalLost-19419.3
2009NetGain41694.05
I need to make sure that years is ordered as it is and the 2nd column is also ordered as it is. If it isn't, I could use an order by on the year in descending order. But I couldn't do the same for the second column.
How would I change the unpivot statement to fix this if it only just happens to go this way?
Thanks,
Tom
October 7, 2013 at 1:21 pm
Quite simple. If you want your results in a given order, you need to add an ORDER BY to your query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2013 at 2:35 pm
It appears that unpivot sets the sort order according to the order given in the IN clause, which is what you want anyway.
If you don't trust that, or something more complicated is going on, the simple solution is to use a CASE statement on activities in your order by clause.
example (you can validate it by mixing up the order in your in clause with and without the extra sort).
WITH myCTE as (
SELECT 2013 AS BoundOrLostYear,
CAST(29544.48 AS MONEY) AS TotalNew,
CAST(-10832.00 AS MONEY) AS TotalLost,
CAST(18712.48 AS MONEY) AS Netgain
UNION
SELECT 2012,
35549.67,
-20252.65,
15297.02
UNION
SELECT 2011,
136816.89,
-22860.54,
113956.35
UNION
SELECT 2010,
45795.48,
-54933.17,
-9137.69
UNION
SELECT 2009,
61113,
-19419.3,
41694.5)
SELECT ActivityYear,Activities, Activity
FROM
(
SELECT BoundOrLostYear AS ActivityYear ,
Totalnew ,
TotalLost ,
NetGain
FROM myCTE
) p
UNPIVOT(Activity FOR Activities IN (Totalnew, TotalLost,NetGain )) b
order by ActivityYear desc, CASE Activities WHEN 'Totalnew' Then 1 WHEN 'TotalLost' Then 2 WHEN 'NetGain' THen 3 END
October 7, 2013 at 2:49 pm
That was what I figured - that I set the order in the "IN" clause. But I wasn't sure if that was how it worked.
But the other issue is the Years. I want them in descending order. And if I add the order by for the Years, would the order of the Activities column doesn't change. If it does, then I would need to add some sort of order number for these. Not sure How I would add that.
Thanks,
Tom
October 7, 2013 at 2:57 pm
Nevyn (10/7/2013)
It appears that unpivot sets the sort order according to the order given in the IN clause, which is what you want anyway.If you don't trust that, or something more complicated is going on, the simple solution is to use a CASE statement on activities in your order by clause.
And you shouldn't trust it. That may be the behavior currently but that is not a documented feature of PIVOT or UNPIVOT. As with any query, if you want the results ordered, use the order by clause.
Just because you are using PIVOT/INPIVOT the same logic holds as you will find in this article.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2013 at 4:02 pm
tshad (10/7/2013)
That was what I figured - that I set the order in the "IN" clause. But I wasn't sure if that was how it worked.But the other issue is the Years. I want them in descending order. And if I add the order by for the Years, would the order of the Activities column doesn't change. If it does, then I would need to add some sort of order number for these. Not sure How I would add that.
Thanks,
Tom
Look at my example above. It sorts by years descending and activities as specified.
October 7, 2013 at 5:03 pm
You're right - that was it.
Didn't see the order by for some reason.
Thanks,
Tom
October 7, 2013 at 6:02 pm
Tom - You might want to look at the first article in my signature links for a (sometimes) faster way to UNPIVOT.
Doesn't do the ordering for you though. You still need to use ORDER BY.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply