SQL: Unrolling a set

Question

Suppose I have a set in SQL like this:

    Product | Quantity
       A         1
       B         2

I want (in a single SELECT statement) to transform that to:

    Product 
       A    
       B    
       B

Can anyone point me towards (T-SQL preferably), a trick on how to do this?

Answer 1

Try CURSOR??

DECLARE @Cursor CURSOR
DECLARE @Product varchar(50)

SET @Cursor = CURSOR FOR
SELECT Product FROM dbo._YourTable

OPEN @Cursor

FETCH NEXT FROM @Cursor INTO @Product 

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @Count int
SET @Count = 0

DECLARE @Quantity int
SET @Quantity = SELECT Quantity FROM dbo._YourTable WHERE Product = @Product

WHILE @Count < @Quantity
BEGIN

SELECT @Product as Product
@Count = @Count + 1

END

FETCH NEXT FROM @Cursor INTO @Product
END

DEALLOCATE @Cursor

Answer 2

You need an intermediary numbers table, or a table-valued function (if that option is available to you) which will produce numbers.

Assuming you had a Numbers table, which is populated like so:

    Number
    ------
         1
         2
       ...
    100000

(or as large as you need it to be, and there are efficient mechanisms for generating a numbers table of large size)

You would then issue the following query:

select
    p.Product
from
    Products as p
        inner join Numbers as n on n.Number <= p.Quantity 

This would produce the result you want.

A numbers table is incredibly helpful in SQL, and Itzik Ben-Gan goes into it and other great querying techniques in his books (listed on his website). I highly recommend them.

Answer 3

select ProductName, Quantity from Product

ProductName Quantity

---

A 1

B 2

C 4

Here is one select, as you wish (sql server 2005):

with result (ProductName, Q)
as
(
    select ProductName, 1 from Product where Quantity > 0
    union all
    select p1.ProductName, result.q + 1 from Product p1
    inner join result on result.ProductName = p1.ProductName
    where result.q < p1.Quantity 
)
select p2.ProductName from Product p2
    inner join result on result.ProductName = p2.ProductName
order by 1
OPTION (MAXRECURSION 0);

ProductName

A
B
B
C
C
C
C