Query Help

  • 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

  • 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

  • Brilliant!!! Thanks.

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

  • 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