Post reply

Query Help

  • August 25, 2017 at 1:42 pm

    #338817

    Here is the basic schema of table
    CREATE TABLE Test (ID INT, ProductCode VARCHAR(50), Quantity INT)

    INSERT INTO Test
    SELECT 1,'ABC', 1
    UNION
    SELECT 2, 'DEF', 2
    UNION
    SELECT 3, 'GEH', 1
    UNION
    SELECT 4, 'XYZ', 3

    This is the result I want. Basically, when quantity is greater than 1, I want additional lines to be returned by the query. So for e.g , if quantity is 2, i want two exact lines but quantity needs to be 1 and 1; if quantity is 3, I want 3 exactlines but quanity needs to be 1,1 and 1 as below.

    Any idea would be much appreciated.

    SELECT * FROM TEST

    1    ABC    1
    2    DEF    1
    2    DEF    1
    3    GEH    1
    4    XYZ    1
    4    XYZ    1
    4    XYZ    1

  • August 25, 2017 at 1:57 pm

    #1956942

    Wwhat about this?

    WITH TEMP_CTE AS(
    SELECT ID, ProductCode, Quantity - 1 AS Quantity FROM Test
    UNION ALL
    SELECT ID, ProductCode, Quantity - 1 FROM TEMP_CTE RECUR WHERE RECUR.Quantity > 0
    )
    SELECT ID, ProductCode, 1 FROM TEMP_CTE
    ORDER BY ID

  • August 25, 2017 at 2:03 pm

    #1956943

    Brilliant!!! Thanks.

  • August 25, 2017 at 2:12 pm

    #1956947

    If quantities get to 100 or more, you are likely to blow your max recursion limit & will need to use a different technique.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 29, 2017 at 7:19 am

    #1957227

    Phil Parkin - Friday, August 25, 2017 2:12 PM

    If quantities get to 100 or more, you are likely to blow your max recursion limit & will need to use a different technique.

    Just an FYI, you can control that limit using OPTION (MAXRECURSION n))
    The valid values for n are from 0 to 32K, and 0 means unlimited.   Of course, specify 0 and it becomes possible to enable an infinite loop.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • August 29, 2017 at 7:29 am

    #1957231

    sgmunson - Tuesday, August 29, 2017 7:19 AM

    Phil Parkin - Friday, August 25, 2017 2:12 PM

    If quantities get to 100 or more, you are likely to blow your max recursion limit & will need to use a different technique.

    Just an FYI, you can control that limit using OPTION (MAXRECURSION n))
    The valid values for n are from 0 to 32K, and 0 means unlimited.   Of course, specify 0 and it becomes possible to enable an infinite loop.

    Thanks Steve. I used the word 'likely' because I know that it can be changed.

    But personally, I prefer sticking at 100 and using other techniques where this limit would be exceeded.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 29, 2017 at 5:22 pm

    #1957339

    With a tally table, this is pretty easy:

    DECLARE @max-2 INT;
    SELECT @max-2 = MAX(Quantity) FROM #Test;

    SELECT ID
        , ProductCode
        , Quantity
    FROM #Test te
    INNER JOIN Scratchpad.dbo.TallyN(@Max) t
    ON t.N <= te.Quantity;

    No recursion required, right?

  • August 31, 2017 at 3:37 pm

    #1957756

    Another option 

    ;with dummy as
    (
        select ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rowid
         from string_split(SPACE(1000),' ')
    )
    select ord.ID, ord.ProductCode, 1 Qty
    from #Test ord
    inner join dummy d on d.rowid<=ord.Quantity
    order by ord.id

Viewing 8 posts - 1 through 7 (of 7 total)

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