Pivot a table

  • Hi All,

    following is the result of my query

    Year_Month.........cat1_per......cat2_per........cat3_per......cat4_per

    2004_06...............0.892..........0.778............0.467..........0.871

    2005_10...............0.790..........0.629............0.581..........0.978

    but i want output of my query to be

    Category...........2004_06..............2005_10

    cat1_per.............0.892..................0.790

    cat2_per.............0.778..................0.629

    cat3_per.............0.467..................0.581

    cat4_per.............0.871..................0.978

    what is the best way to do it? any help is appreciated. Thanks you in advance.

  • You should take a look at this article

    http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

    Most in here would say you want to unpivot the table 😀

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (10/16/2012)


    You should take a look at this article

    http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

    Most in here would say you want to unpivot the table 😀

    :-P:-P I was going to say that! :hehe::hehe:


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • Without seeing your query, it's impossible to tell what the best approach may be. The previous posts will certainly work, but it's entirely possible that your original query may be doing on unnecesary pivot or crosstab and removing that pivot/crosstab will solve your problem.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply