sql - 将值转换为单行

Question

当前

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

请求的结果

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

我该怎么做？

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

Answer 1

如果已知quantity列中的最大值小于32,767，您可以使用递归生成数字并将数字连接起来以获得结果。

/*******************************************
  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);