Pivot a table

Question

Post reply

Pivot a table

myjobsinus

SSC Eights!

Points: 964
More actions
October 16, 2012 at 2:11 pm

#263421

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.

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 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

drew.allen SSC Guru Points: 76985 More actions · Answer 3

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