Post reply

Split single row into multiple rows based on column value (quantity)

  • January 30, 2015 at 6:40 am

    #314193

    Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.

    SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE

    FROM @SPLITROW s

    INNER JOIN master.dbo.spt_values t ON t.type='P'

    AND t.number BETWEEN 1 AND s.QTY

    Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?

    Thanks

    B

  • February 12, 2015 at 8:48 pm

    #1777400

    Heh... it doesn't matter. MS reserves the right to change anything at any time.

    If you follow what a lot of folks say, they recommend avoiding undocumented or non-Ansi features. I'm not one of those.

    I will say that using that table is a bit of a pain, though. It's also about 2-1/2 times slower than a dedicated Tally Table and uses about 2-1/2 times more reads because the table is wider.

    I maintain both a Tally Table and a Tally Table function. It's just easier for me 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)

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

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