SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Order with unpivot


Order with unpivot

Author
Message
tshad
tshad
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 454
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:

Year TotalNew TotalLost NetGain
2013 29544.48 -10832.00 18712.48
2012 35549.67 -20252.65 15297.02
2011 136816.89 -22860.54 113956.35
2010 45795.48 -54933.17 -9137.69
2009 61113.35 -19419.3 41694.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.


Year Activities Activity
2013 Totalnew 29544.48
2013 TotalLost -10832
2013 NetGain 18712.48
2012 Totalnew 35549.67
2012 TotalLost -20252.65
2012 NetGain 15297.02
2011 Totalnew 136816.89
2011 TotalLost -22860.54
2011 NetGain 113956.35
2010 Totalnew 45795.48
2010 TotalLost -54933.17
2010 NetGain -9137.69
2009 Totalnew 61113.35
2009 TotalLost -19419.3
2009 NetGain 41694.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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61977 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 3149
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


tshad
tshad
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 454
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61977 Visits: 17954
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.

http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 3149
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.
tshad
tshad
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 454
You're right - that was it.

Didn't see the order by for some reason.

Thanks,

Tom
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17589 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search