Post reply

Convert rows into columns

  • March 3, 2006 at 4:06 pm

    #111998

    Hi, I have the following table structure

    ID   Jan   Feb   March   April  May ...  Dec

    1     a       b      c         d      e   ...   z

    2     aa     bb     cc       dd    ee  ...   zz

    and so on

    How could I turn those month columns into rows like this

    1    a    2005-01-01

    1    b    2005-02-01

    1    c    2005-03-01

    1    d    2005-04-01

    1    e    2005-05-01

    ...

    1    z    2005-12-01

    2    aa   2005-01-01

    2    bb   2005-02-01

    2    cc   2005-03-01

    2    dd   2005-04-01

    2    ee   2005-05-01

    ...

    2    zz   2005-12-01

     

    Thanks in advance

              Isaac B

  • March 3, 2006 at 5:38 pm

    #624758

    Try this and add the remaining months:

    declare @Table table (ID int, Jan int, Feb int, March int, April int)

    insert @Table values (1, 2, 4, 6, 8)

    insert @Table values (2, 3, 5, 7, 9)

    select id, convert(datetime, '01 Jan 2006') as Date, jan as amount

    from @Table

    union all

    select id, convert(datetime, '01 Feb 2006') as Date, feb

    from @Table

    union all

    select id, convert(datetime, '01 Mar 2006') as Date, march

    from @Table

    union all

    select id, convert(datetime, '01 Apr 2006') as Date, april

    from @Table

    order by 1, 2

  • March 4, 2006 at 12:43 am

    #624766

    in study

  • March 4, 2006 at 2:40 am

    #624768

    Similar post was just posted with some answers already provided

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=263270&sub=1

     

  • October 16, 2007 at 10:04 pm

    #742575

    Hi, I'm having problems is converting the month period into columns.

    Actually my scenario is:

    - item A sold in 01/01/07 = 10

    - item A sold in 10/01/07 = 20

    total item A sold in "JAN" = 30

    - item A sold in 01/03/07 = 5

    - item A sold in 21/03/07 = 10

    total item A sold in "MAR" = 15

    At first, I did managed to sum up the quantity but it display as row (in which I need it as column) and the output is:

    Item Qty Period

    ----- ----- ---------

    A 30 2007-01

    A 15 2007-03

    I need the layout to be like this:

    Item Jan Feb Mar

    ---- ---- ---- ----

    A 30 - 15

    Please advise, any help will be much appreciated. Thanks.

    cheers,

    serene

  • October 16, 2007 at 10:52 pm

    #742587

    This type of formatting should generally be done in a GUI...

    However, it can be done in SQL Server... lookup "Cross-Tab Reports" in Books Online for a complete explanation of how to do these...

    --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)

  • October 14, 2011 at 1:36 pm

    #1394840

    This works like Charm for me... thanks JeffB

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

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