Post reply

Help : turning column header into column values

  • May 17, 2006 at 8:53 pm

    #168305

    Hai all,

    I have a table that look like this :

    ID MATH BIO CHEM ENG

    1   8    8   8    8

    2   7    7   7    7

    I want to convert into :

    SUBJECT VALUE ID

    MATH     8        1

    BIO        8        1

    CHEM     8        1

    ENG       8        1

    MATH     7        2

    BIO        7        2

    CHEM     7        2

    ENG       7        2

    If anyone knows how to do it, please help...

    Thanks.

    -thuthu-

  • May 18, 2006 at 6:57 am

    #638696

    Check out Pivot and Cross-tab reports in Books Online.

  • May 18, 2006 at 8:38 am

    #638754

    UNPIVOT is the key, here:

    DECLARE
     @test table(id int, math int, bio int, chem int, eng int)
     INSERT
     @test SELECT 1, 8, 8, 8, 8 UNION SELECT 2, 7, 7, 7, 7
     SELECT
     * FROM @test
     
     SELECT
     Subject, val AS [Value], ID   FROM (SELECT ID, Math, Bio, Chem, Eng           FROM @test) tst        UNPIVOT           (val FOR Subject IN (Math, Bio, Chem, Eng)) AS unpvt
     

    -Eddie

    Eddie Wuerch
    MCM: SQL

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

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