Home Forums SQL Server 2008 T-SQL (SS2K8) Convert years RE: Convert years

  • October 18, 2012 at 4:29 pm

    #1550698

    hugo_17_23 19942 (10/18/2012)

    Hi,

    Can Anyone help me with this, please

    I have a Table like this

    Es Year January February March

    E0003 2008 XXXXX XXXXX XXXXX

    E0003 2009 XXXXY XXXXY XXXXY

    E0004 2008 XXXYY XXXYY XXXYY

    E0004 2009 ZZYYY ZZYYY ZZYYY

    And I need to convert that table into this:

    Es Jan_08 Jan_09 Feb_08 Feb_09 Mar_08 Mar_09

    E0003 XXXXX XXXXY XXXXX XXXXY XXXXX XXXXY

    E0004 XXXYY ZZYYY XXXYY ZZYYY XXXYY ZZYYY

    You will need to "unpivot" or "uncrosstab" the data so that it's in a vertical format and then "repivot" or "recrosstab" to the desired layout.

    For coded help, please see the first link in my signature line below.

    As a sidebar, the original data is stored as if it were a spreadsheet, which is a denormalized form and causes a lot of the problems like the very problem you're currently having. My recommendation is that once you have "unpivoted" the data, you keep it that way.

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