Post reply

need help transforming a table's column to rows

  • June 21, 2009 at 3:07 am

    #1012983

    Jeff Moden (6/20/2009)

    Florian Reischl (6/20/2009)

    If you don't know a Table or how to work with please search this site. You will find a really good article published by Jeff Moden which explains the Tally table and how to use it for requirements like this.

    Flo

    Thanks for the plug, Flo. 🙂 The article on what a Tally table is and how it replaces a loop in many cases, can be found at the following URL...

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    One of my standard sentences :-).

  • June 21, 2009 at 2:44 pm

    #1013080

    :blush::-)

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

  • June 22, 2009 at 2:44 pm

    #1013588

    ktlady (6/20/2009)

    Wayne, I added "select * from @Tmp" at end of your code. And here is the result I get from.

    ColumnAColumnBTmpCol

    a11:3:5:61356

    a22:4:5245

    Does this match the expectation?

    Yes, that is what was inserted into that table.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 22, 2009 at 2:46 pm

    #1013589

    Jeff Moden (6/20/2009)

    Heh... they kind of did :hehe: ... take a look at the NUMBER column of Master.dbo.spt_Values where the TYPE = 'P'. I don't believe they ever intended it to be used as a short Tally table, but it is there.

    And a short tally table, combined with a wee bit of recursive CTE, can quickly become a large one.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 22, 2009 at 4:21 pm

    #1013631

    WayneS (6/22/2009)

    Jeff Moden (6/20/2009)

    Heh... they kind of did :hehe: ... take a look at the NUMBER column of Master.dbo.spt_Values where the TYPE = 'P'. I don't believe they ever intended it to be used as a short Tally table, but it is there.

    And a short tally table, combined with a wee bit of recursive CTE, can quickly become a large one.

    True enough..... but the Recursive CTE will always provide a performance problem. Sure, sure... it might only be used once to create a larger permanent Tally table, but then you're practicing RBAR instead of practicing one of the many other right ways to do it using set based code.

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

  • June 22, 2009 at 11:22 pm

    #1013724

    Jeff Moden (6/22/2009)

    WayneS (6/22/2009)

    Jeff Moden (6/20/2009)

    Heh... they kind of did :hehe: ... take a look at the NUMBER column of Master.dbo.spt_Values where the TYPE = 'P'. I don't believe they ever intended it to be used as a short Tally table, but it is there.

    And a short tally table, combined with a wee bit of recursive CTE, can quickly become a large one.

    True enough..... but the Recursive CTE will always provide a performance problem. Sure, sure... it might only be used once to create a larger permanent Tally table, but then you're practicing RBAR instead of practicing one of the many other right ways to do it using set based code.

    Hmmmm. Good point.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 6 posts - 16 through 20 (of 20 total)

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