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


Order with unpivot


Order with unpivot

Author
Message
tshad
tshad
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26430 Visits: 17557
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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1536 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
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26430 Visits: 17557
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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1536 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
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

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

Didn't see the order by for some reason.

Thanks,

Tom
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7429 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