How do I create duplicate rows in a table with a count? (SQL)
Posted on 2016-07-19
I have a table with a single row for each order. It has an order#, tag, and quantity. If it has a quantity greater than 1, I need to duplicate the rows (quantity - 1) number of times because the tag can be unique for each item of the quantity.
I use to have a table with the tags in it with number of rows equal to the quanity that I could join on to update the tags and create mulitiple records via the join. The table is going away so now I have come up with a way to duplicate the rows, add a quantity count, and update the tags (by looking up order in a table and finding all the tags for the order and updating the field).
Order# Tag Qty
I need it to look like this (will need to add a field for QtyCount)
Order# Tag Qty QtyCount
1 Ace 5 1
1 Spd 5 2
1 Jack 5 3
1 King 5 4
1 Club 5 5
2 One 1 1
I don't know how to generate the extra rows for the ones with quantity greater than one and then how do I increment the QtyCount field (1 of 5, 2 of 5, etc.)
I'm using SQL Server 2008 R2.