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

Pivot a table Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 2:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 1, 2014 12:34 PM
Points: 23, Visits: 106
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.
Post #1373517
Posted Tuesday, October 16, 2012 2:25 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 4,066, Visits: 9,235
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1373525
Posted Tuesday, October 16, 2012 11:31 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: Tuesday, December 23, 2014 1:40 AM
Points: 3,443, Visits: 5,404
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


I was going to say that!



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 #1373629
Posted Wednesday, October 17, 2012 8:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1373871
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse