Pivot tha data in this format

  • hi

    My data is in this format

    jan-2008 feb-2008 mar-2008

    Actual 100 200 300

    Budget 200 500 800

    and i want the result like this

    provided the column names and the data.

    Actual/Budget Revenue Period/Mon period/Year

    Actual 100 jan 2008

    Budget 200 jan 2008

    Actual 200 Feb 2008

    Budget 500 Feb 2008

    please do this needful and thanks in advance.

  • Create table forecast (

    typevarchar(10),

    [jan-2008]int,

    [feb-2008]int,

    [mar-2008]int )

    Insert into forecast values ('Actual', 100, 200, 300)

    Insert into forecast values ('Budget', 200, 500, 800)

    Select* from forecast

    SELECT *

    FROMforecast

    UNPIVOT (Amount FOR MonthYear IN ([jan-2008],[feb-2008],[mar-2008] )) AS u

  • Hi,

    How can we handle this reversal of pivoting in 2000?

    ARUN SAS

  • using a cross join

    SELECTa.Type, c.MonthYear,

    Sum(CASE WHEN c.MonthYear='jan-2008' THEN a.[jan-2008]

    WHEN c.MonthYear='feb-2008' THEN a.[feb-2008]

    WHEN c.MonthYear='mar-2008' THEN a.[mar-2008]

    END) Amount

    FROM forecast a

    CROSS JOIN (SELECT 'jan-2008' as MonthYear UNION SELECT 'feb-2008' UNION SELECT 'mar-2008')c

    GROUP BY a.Type, c.MonthYear

  • thanks for giving reply. in result i want

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

    actuals/budget | Revenue | Month | year |

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

    Actuals |100 |jan |2008 |

    BHudget |200 |feb |2008 |

    i want month and year in different column. please help me out in this.

  • Use the above query as a subquery and do the conversion in the outer query.

    -Arun

  • Looking at the schema, and I use the term loosely, of your input table, I assume you are pulling this from a spreadsheet and that you won't have hundreds of thousands of rows being processed. If this is something you are doing a few times a day, and with only a few thousand rows at a time, the following should be adequate for the purpose.

    You should probably also hard code a year/month number instead of the characters for month, in order to keep the order of rows correct. Take a look at the orderBY column I'm creating in the code below. If you prefer the order by to be a datetime column so you could join to datetime columns in other tables, just change 200801, to cast('1/1/2008' as datetime). Having separate month and year columns in your tables is generally not a good practice.

    Good luck.

    declare @sample table (AB char(6), [Jan-2008] int, [Feb-2008] int, [Mar-2008] int)

    insert into @sample

    select 'Actual', 100, 200, 300 union all

    select 'Budget', 200, 500, 800

    --set statistics io on;

    --set statistics time on;

    select AB,[Jan-2008] as revenue,'Jan' as revenueMonth,2008 as revenueYear,200801 as orderBy

    from @sample

    union all

    select AB,[Feb-2008],'Feb',2008,200802

    from @sample

    union all

    select AB,[Mar-2008],'Mar',2008,200803

    from @sample

    order by orderBy, AB

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • vkoka (4/22/2009)


    thanks for giving reply. in result i want

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

    actuals/budget | Revenue | Month | year |

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

    Actuals |100 |jan |2008 |

    BHudget |200 |feb |2008 |

    i want month and year in different column. please help me out in this.

    Actually, if you intend to store this in a table, you want year, month, and day all in the same column as a DATETIME datatype. To do otherwise will cause a serious world of pain in the near future because of all the conversions you'll need to make to do other things. Once you've done that, you can use calculated columns for displaying just the year and month without the problems associated with actually storing such things in a table.

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

  • thanks u very much for u'r guidence..

  • Thanks for the feedback. I always wonder if some of these suggestions get through or not.

    --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 10 posts - 1 through 9 (of 9 total)

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