Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Not sure how to transpose a table like this... Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, February 19, 2014 5:08 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, February 20, 2014 10:31 AM Points: 2, Visits: 5
 Let's assume I have a table like thisSELECT * FROM PointsPointID Axis ValueP1 X 10P1 Y 20P2 X 30P2 Y 40I was wondering what query can I write that can return the followingPointID X YP1 10 20P2 30 40Any suggestions?thanks
Post #1543288
 Posted Wednesday, February 19, 2014 5:13 PM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, September 23, 2015 3:34 PM Points: 5,467, Visits: 7,660
 The keywords you want to use to search for are either PIVOT or CROSSTAB. Either method will work for you. - Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake. For better assistance in answering your questions | Forum NetiquetteFor index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA
Post #1543291
 Posted Wednesday, February 19, 2014 5:42 PM This worked for the OP
 Forum Newbie Group: General Forum Members Last Login: Thursday, February 20, 2014 10:31 AM Points: 2, Visits: 5
 ThanksThe query would beselect PointID, X, Yfrom Pointspivot( max(Value) for Axis in (X, Y))
Post #1543297
 Posted Wednesday, February 19, 2014 9:39 PM This worked for the OP
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 3:23 PM Points: 42,036, Visits: 39,415
 In many cases, people who have these types of Pivots also eventually want a "Total" column. The ancient "Black Art" of CROSSTABs easily allows for such a thing.` SELECT PointID ,X = SUM(CASE WHEN Axis = 'X' THEN Value ELSE '' END) ,Y = SUM(CASE WHEN Axis = 'Y' THEN Value ELSE '' END) ,Total = SUM(Value) FROM cteTestData GROUP BY PointID;`Result Set:`PointID X Y Total------- ----------- ----------- -----------P1 10 20 30P2 30 40 70`CROSSTABs also make it pretty easy to do other things. For example...` SELECT PointID = CASE WHEN GROUPING(PointID) = 0 THEN PointID ELSE 'Total' END ,X = SUM(CASE WHEN Axis = 'X' THEN Value ELSE '' END) ,Y = SUM(CASE WHEN Axis = 'Y' THEN Value ELSE '' END) ,Total = SUM(Value) FROM cteTestData GROUP BY PointID WITH ROLLUP;`Result Set:`PointID X Y Total------- ----------- ----------- -----------P1 10 20 30P2 30 40 70Total 40 60 100`The following article also explains how to do multiple column types in Pivots and CROSSTABs, explains the performance advantages of "pre-aggregation", along with a demonstration that pre-aggregatedCROSSTABs are frequently almost twice as fast as pre-aggregated Pivots. It can really make a difference in being able to meet and beat reporting SLAs. The second link demonstrates the simple power of dynamic CROSSTABs for things like perpetual sliding window reports with no code change based on GETDATE() alone.http://www.sqlservercentral.com/articles/T-SQL/63681/http://www.sqlservercentral.com/articles/Crosstab/65048/To be honest, I don't ever use Pivot. I always use CROSSTABs, instead, for all the reasons previously stated and for their ease in readability. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #1543320

 Permissions