Post reply

How to transpose the ROWs

  • December 26, 2008 at 4:22 am

    #73165

    Hi ,

    I want to How to transpose the ROWs from table.

    eg.

    Exiting format

    ID Status Count

    0001 XX 10

    0004 YY 30

    0005 YX 40

    i want in below format

    ID XX YY YX

    0001 10 NULL NULL

    0004 NULL 30 NULL

    0005 NULL NULL 40

    and also i want the MAX value of the ID

    (ROW wise MAX value)

    Thanks and Regards,

    Pravin V. Kadam

  • December 26, 2008 at 5:11 am

    #917742

    Read about Cross-tab reports in sql server help file

    Madhivanan

    Failing to plan is Planning to fail

  • December 26, 2008 at 7:02 am

    #917765

    try this:

    CREATE TABLE #PAPI

    (

    ID CHAR(4),

    STATUS CHAR(2),

    CNT INT

    )

    INSERT INTO #PAPI

    SELECT '0001', 'XX', 10

    UNION ALL

    SELECT '0004', 'YY', 30

    UNION ALL

    SELECT '0005', 'XY', 40

    SELECT * FROM #PAPI

    SELECT ID,

    MAX(CASE [STATUS] WHEN 'XX' THEN cnt ELSE null END) AS XX,

    MAX(CASE [STATUS] WHEN 'YY' THEN cnt ELSE null END) AS YY,

    MAX(CASE [STATUS] WHEN 'XY' THEN cnt ELSE null END) AS XY

    FROM #PAPI

    GROUP BY ID

    Hope it works to you ...if you have any problem be more specific and post the structure of the table then some sample data again!

    Dugi

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • December 26, 2008 at 10:29 am

    #917862

    Praveen,

    If you want to learn how to make cross tabs, then what Madhivanan suggested is a good idea. Also, I wrote a kind-of tutorial on how to make a crosstab at the following URL...

    http://qa.sqlservercentral.com/articles/T-SQL/63681/

    --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)
    Intro to Tally Tables and Functions

  • December 26, 2008 at 9:35 pm

    #917978

    Thanks you very much Jeff !!

    It's really helpful article

    Thanks and Regards,

    Pravin V. Kadam

  • December 27, 2008 at 9:06 am

    #918027

    Thanks Pravin... I always appreciate the feedback.

    --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)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

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