Turn the value into a 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

Using recursion and borrowing Michael Fredrickson installation 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

It seems to work faster on my mailbox (5 times faster than Michael Fredrickson according to the query plan, but with a lot more logical readings), but this is not so important.

Answer 2

If the maximum value in the column quantityis known to be less than 32,767, you can use it Recursionto generate numbers and combine 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 3

, :

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

declare @numbers table (
  Number int
)

insert into @numbers values (1)
insert into @numbers values (2)
insert into @numbers values (3)
insert into @numbers values (4)
-- Etc... up to however many numbers is the max possible value for sales...

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

SELECT
    t.Name,
    1 AS Sales
FROM
    @T t JOIN
    @numbers n ON
        t.Sales >= n.Number
ORDER BY t.Name

, , .