Insert a row, based on a fields value

Question

My data looks like this

Supplier  Qty  
--------  ---  
ABC       3
BCD       1  
CDE       2  
DEF       1

I expect the result to be:

Supplier  Qty  
--------  ---  
ABC       3   }---> Add additional row based on the number of qty
ABC       3   }
ABC       3   }
BCD       1  
CDE       2   }---> Add additional row here too
CDE       2   }
DEF       1

Looking for an sql SELECT statement that performs the expected result.
I am using Sql Server 2008

is there a maximum in QTY that is allowed or could there be any number? — xQbert
– xQbert, Commented Jun 13, 2012 at 17:08
So you're attempting to make the number of rows per Supplier equal to the Qty — swasheck
– swasheck, Commented Jun 13, 2012 at 17:13
possible duplicate of Duplicating rows based on a column value in each row — Aaron Bertrand
– Aaron Bertrand, Commented Jun 13, 2012 at 17:15

Aaron Bertrand · Accepted Answer · 2012-06-13 17:29:15Z

6

DECLARE @d TABLE (Supplier VARCHAR(32), Quantity INT);

INSERT @d SELECT 'ABC',3
UNION ALL SELECT 'BCD',1  
UNION ALL SELECT 'CDE',2  
UNION ALL SELECT 'DEF',1;

WITH x AS 
(
  SELECT TOP (10) rn = ROW_NUMBER() --since OP stated max = 10
  OVER (ORDER BY [object_id]) 
  FROM sys.all_columns 
  ORDER BY [object_id]
)
SELECT d.Supplier, d.Quantity
FROM x
CROSS JOIN @d AS d
WHERE x.rn <= d.Quantity
ORDER BY d.Supplier;

edited Jun 13, 2012 at 17:29

answered Jun 13, 2012 at 17:19

Aaron Bertrand

282k37 gold badges468 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

whytheq Over a year ago

(...double the points! Nice work Aaron) It is a great query - is there not a prettier way of filling you CTE?

Aaron Bertrand Over a year ago

@whytheq like what? Note that the OP just stated that the max is 10.

whytheq Over a year ago

I asked the question! All it returns is a list of numbers 1,2,3..Max; I just thought there might be a prettier way of getting it.

Aaron Bertrand Over a year ago

So are you Nishal or whytheq? Anyway SQL Server doesn't have a built-in table of numbers. You could manually write out SELECT rn = 1 UNION SELECT rn = 2 etc. but I find the above "prettier" even if it means it accesses a catalog view.

Nishal Over a year ago

Perfect! Many thanks for your answer. (Up vote and accepted your answer)

|

Bort · Accepted Answer · 2012-06-13 17:25:44Z

Assuming you are inserting back into the same table, using a CTE to figure out what rows to insert works cleanly. This will not work if the Quantity is greater than the max recursion level (which I believe is 100 by default) and so may not be an appropriate solution.

;WITH SupplyToInsert (Supplier, Qty) AS (
    SELECT Supplier, Qty - 1
    FROM Supply
    WHERE Qty > 1

    UNION ALL

    SELECT S.Supplier, I.Qty - 1
    FROM Supply S
        INNER JOIN SupplyToInsert I ON I.Supplier = S.Supplier
    WHERE I.Qty > 1
)
INSERT INTO Supply (Supplier, Qty)
SELECT I.Supplier, S.Qty
FROM SupplyToInsert I
    INNER JOIN Supply S ON S.Supplier = I.Supplier

If you arent inserting back into the same table, change each Qty > 1 to Qty > 0 in the CTE, to get one extra row for each supplier.

Working SqlFiddle

+1 Creative. For my own education: is ;with cte a purely SQL solution or does it require T-SQL functions to work? I guess I'm asking is ;with cte a strictly SQL answer.

xQbert · Accepted Answer · 2012-06-13 17:11:41Z

By no means the prettiest of things; and assumes QTY will never exceed 10. There are better ways to do this... but would involve PL\SQL or T-SQL can't think of a straight SQL answer.

Insert into yourTablename
(
(Select supplier, Qty From YourTableName where Qty-1 > 0) 
UNION
(Select supplier, Qty From YourTableName where Qty-2 > 0) 
UNION
(Select supplier, Qty From YourTableName where Qty-3 > 0) 
UNION
(Select supplier, Qty From YourTableName where Qty-4 > 0) 
UNION
(Select supplier, Qty From YourTableName where Qty-5 > 0) 
UNION
(Select supplier, Qty From YourTableName where Qty-6 > 0) 
UNION
(Select supplier, Qty From YourTableName where Qty-7 > 0) 
UNION
(Select supplier, Qty From YourTableName where Qty-8 > 0) 
UNION
(Select supplier, Qty From YourTableName where Qty-9 > 0) 
UNION
(Select supplier, Qty From YourTableName where Qty-10 > 0) 
)

Furthermore this is a one shot deal; if this doesn't do it right the first time; you'd have to clean up the data and then try again. (again not my favorite answer but limiting to JUST SQL...)

can you just change the above to a loop with the condition involving the maximum value of Qty
Yes, but a LOOP involves T-SQL and is not strictly a SQL answer.

Collectives™ on Stack Overflow

Insert a row, based on a fields value

3 Answers 3

6 Comments

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

2 Comments

Linked

Related