Turn value into singular row

Question

Current

Name   Quantity
---------------
Stella       2
Jennifer     2
Greg         3 

Requested result

Name    Quantity 
---------------
Stella       1
Stella       1
Jennifer     1
Jennifer     1
Greg         1
Greg         1 
Greg         1 

How should I do it?

declare @T table  
(  
  Name varchar(50),  
  Sales int  
)  
insert into @T values  
('Stella',   '2'),  
('Jennifer', '2'), 
('Greg',     '3')

Answer 1

If the maximum value in the quantity column is known to be less than 32,767, you can use Recursion to generate numbers and join the Numbers to achieve your result.

/*******************************************
  Max Recursion Count in SQL Server is 32767
  Limitation of 32767 Numbers!
 ******************************************/
;WITH Numbers (Number) AS 
(
    SELECT 1 
    UNION ALL
    SELECT 1 + Number FROM Numbers WHERE Number < 100
)
SELECT  m.Name,
        Quantity = 1
  FROM  MyTable m 
        JOIN @numbers n ON m.Quantity <= n.Number
OPTION (MAXRECURSION  32767);

Answer 2

Using recursion and borrowing Michael Fredrickson's setup code:

declare @T table (  
  Name varchar(50),  
  Sales int  
)

insert into @T values ('Stella',   '2')  
insert into @T values ('Jennifer', '2') 
insert into @T values ('Greg',     '3')

-- Recursive verion
;with People (Name, Sales) as
(
    select Name, Sales
    from @T
    union all
    select Name, Sales - 1
    from People
    where Sales - 1 > 0
)
select Name, 1 as Quantity
from People
option (maxrecursion 0) -- Recurse without limit

This seems to run faster on my box (5x faster than Michael Fredrickson's according to query plan, but with many more logical reads), not that it matters much.