Convert qty column into multiple rows 1

[Luvsql]

I have a table like the following:

Code Qty
CODE1 12
CODE2 5
CODE3 10

What I need to do is create x amount of unique rows for each code where x is the quantity by an incremeting value. The value I could actually do manually with exporting the results to excel, so I somehow need the following results:

Code Qty
CODE1 1
CODE1 1
CODE1 1
CODE1 1
CODE1 1
CODE1 1
CODE1 1
CODE1 1
CODE1 1
CODE1 1
CODE1 1
CODE1 1
CODE2 1
CODE2 1
CODE2 1
CODE2 1
CODE2 1
CODE3 1
etc

Is this possible?

 

[SkipVought]

Select code, sum(qty)
From SomeTable
Group by code

 

[gmmastros]

For this type of query, I would usually use a numbers table, which is really just a table in my database that has a single column with an incrementing number. I don't know if you have a numbers table in your database, so here is a way to get the same results.

Declare @Temp Table(Code VarChar(20), Qty Int)

Insert Into @Temp Values('CODE1', 12)
Insert Into @Temp Values('CODE2', 5)
Insert Into @Temp Values('CODE3', 10)

Select  *
From    @Temp T
        Inner Join (
           Select number From master..spt_values where type = 'P'
        ) As Numbers
        On T.QTY > Numbers.Number

Since this uses a table in the master database, it is limited to the number of rows in that table, which happens to be 2048. If you need numbers larger than that, you should create your own numbers table and use that instead.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom