Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Order with unpivot


Order with unpivot

Author
Message
tshad
tshad
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16515 Visits: 16989
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)
Nevyn
Nevyn
SSC Eights!
SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)

Group: General Forum Members
Points: 956 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
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16515 Visits: 16989
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)
Nevyn
Nevyn
SSC Eights!
SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)

Group: General Forum Members
Points: 956 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
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

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

Didn't see the order by for some reason.

Thanks,

Tom
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

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