Post reply

Not sure how to transpose a table like this...

  • February 19, 2014 at 5:08 pm

    #150629

    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

  • February 19, 2014 at 5:13 pm

    #1690516

    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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • February 19, 2014 at 5:42 pm

    #1690520

    Thanks

    The query would be

    select PointID, X, Y

    from Points

    pivot

    (

    max(Value)

    for Axis in (X, Y)

    )

  • February 19, 2014 at 9:39 pm

    #1690535

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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