Transpose cloumns into rows and rows into columns

  • my result set is like this.But i want to pivot the result.

    I used both the cobination of pivot and unpivot but i got the result but is not worked for me.

    stateOctoberNovemberDecemberJanuaryFebuary

    state17211631 1035 821752

    state213212015 1997 101 515

    state313091360 908 736665

    state42721716 1847 1804897

    state5130366 394 452240

    state62312992 2468 25531693

    state76121637 1419 941339

    state81023941 1772 1641902

    for the above result set Actually i want the result set like this where there is transpose function in sql server.

    state state1state2state3state4state5state6state7state8

    October 721132113092721302316121023

    November 163120151360171636629921637941

    December 103519979081847394246814191772

    January 8211013736180445225539411641

    Febuary 7525156658972401693339902

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • You might want to read these articles:

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

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • I'm not aware of any transpose function in SQL Server.

    So you'd need to UNPIVOT your data to get three columns (state, month and value). Then use one of the links Wayne pointed you at to get the data pivoted again in a transposed for.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hi i got my result .but i did not got my result directly with combination of pivot and unpivot result.first i unpivot the result into temp

    then pivot the temp table.

    finally got succeeded.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • USE tempdb;

    DECLARE @data

    TABLE (

    state CHAR(6) NOT NULL,

    october INT NOT NULL,

    november INT NOT NULL,

    december INT NOT NULL,

    january INT NOT NULL,

    february INT NOT NULL

    );

    INSERT @data

    VALUES ('state1', 0721, 1631, 1035, 0821, 0752),

    ('state2', 1321, 2015, 1997, 0101, 0515),

    ('state3', 1309, 1360, 0908, 0736, 0665),

    ('state4', 0272, 1716, 1847, 1804, 0897),

    ('state5', 0130, 0366, 0394, 0452, 0240),

    ('state6', 0231, 2992, 2468, 2553, 1693),

    ('state7', 0612, 1637, 1419, 0941, 0339),

    ('state8', 1023, 0941, 1772, 1641, 0902);

    SELECT P.month AS state,

    P.state1,

    P.state2,

    P.state3,

    P.state4,

    P.state5,

    P.state6,

    P.state7,

    P.state8

    FROM @data D

    UNPIVOT (value FOR month IN (october, november, december, january, february)) U

    PIVOT (MAX(U.value) FOR U.state IN (state1, state2, state3, state4, state5, state6, state7, state8)) P

    ORDER BY

    CASE P.month

    WHEN 'october' THEN 1

    WHEN 'november' THEN 2

    WHEN 'december' THEN 3

    WHEN 'january' THEN 4

    WHEN 'february' THEN 5

    ELSE NULL

    END;

    For a general Transpose function (implemented as a CLR procedure) see http://www.sqlmag.com/articles/index.cfm?articleid=102631. A subscription is required.

    Paul

  • Hi pual ,

    i got suceeded with pivot and unpivot.But i First transpose this to unpivot then pivot the table.I take two steps for this.

    But you have done this in single steps.thanks for reply.

    your code is pretty ,simple and excellent.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • Thanks very much!

Viewing 8 posts - 1 through 7 (of 7 total)

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