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

Not sure how to transpose a table like this... Expand / Collapse
Author
Message
Posted Wednesday, February 19, 2014 5:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 this

SELECT * FROM Points

PointID Axis Value
P1 X 10
P1 Y 20
P2 X 30
P2 Y 40


I was wondering what query can I write that can return the following

PointID X Y
P1 10 20
P2 30 40

Any suggestions?
thanks
Post #1543288
Posted Wednesday, February 19, 2014 5:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 6,172, Visits: 7,241
The keywords you want to use to search for are either PIVOT or CROSSTAB. Either method will work for you.


- Craig Farrell

Never 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 Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1543291
Posted Wednesday, February 19, 2014 5:42 PM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 20, 2014 10:31 AM
Points: 2, Visits: 5
Thanks
The query would be

select PointID, X, Y
from Points
pivot
(
max(Value)
for Axis in (X, Y)
)
Post #1543297
Posted Wednesday, February 19, 2014 9:39 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,788, Visits: 31,246
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 30
P2 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 30
P2 30 40 70
Total 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1543320
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse