Post reply

Convert years

  • October 18, 2012 at 3:45 pm

    #268768

    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

  • October 18, 2012 at 3:52 pm

    #1550693

    What have you tried, and where are you getting stuck?

    Please read this

    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

    Look up Case statement, and

    group by Es

    Or the Pivot Functions

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

  • October 18, 2012 at 4:30 pm

    #1550700

    I'm sure this article will help you.

    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 18, 2012 at 4:41 pm

    #1550703

    Jeff Moden (10/18/2012)

    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.

    For this query, I'm sure he doesn't need to "unpivot" the data.

    However, I support your recommendation on keep the data normalized.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 27, 2012 at 11:11 pm

    #1553512

    Luis Cazares (10/18/2012)

    Jeff Moden (10/18/2012)

    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.

    For this query, I'm sure he doesn't need to "unpivot" the data.

    However, I support your recommendation on keep the data normalized.

    For hard coded dates, I agree. But how often are hard coded dates going to be safe on the 1st of the next year?

    --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 6 posts - 1 through 5 (of 5 total)

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