Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Order with unpivot Expand / Collapse
Author
Message
Posted Monday, October 7, 2013 1:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:59 AM
Points: 147, Visits: 360
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

Post #1502322
Posted Monday, October 7, 2013 1:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
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 Moden's 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)
Post #1502329
Posted Monday, October 7, 2013 2:35 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:03 PM
Points: 621, Visits: 2,127
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

Post #1502371
Posted Monday, October 7, 2013 2:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:59 AM
Points: 147, Visits: 360
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
Post #1502378
Posted Monday, October 7, 2013 2:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
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 Moden's 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)
Post #1502381
Posted Monday, October 7, 2013 4:02 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:03 PM
Points: 621, Visits: 2,127
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.
Post #1502402
Posted Monday, October 7, 2013 5:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:59 AM
Points: 147, Visits: 360
You're right - that was it.

Didn't see the order by for some reason.

Thanks,

Tom
Post #1502416
Posted Monday, October 7, 2013 6:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:39 PM
Points: 3,640, Visits: 5,287
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!
Post #1502432
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse