sql - 根据字段值插入一行

Question

我的数据看起来像这样

Supplier  Qty  
--------  ---  
ABC       3
BCD       1  
CDE       2  
DEF       1  

我希望结果是:

Supplier  Qty  
--------  ---  
ABC       3   }---> Add additional row based on the number of qty
ABC       3   }
ABC       3   }
BCD       1  
CDE       2   }---> Add additional row here too
CDE       2   }
DEF       1  

寻找执行预期结果的 sql SELECT 语句。
我正在使用 Sql Server 2008

Answer 1

DECLARE @d TABLE (Supplier VARCHAR(32), Quantity INT);

INSERT @d SELECT 'ABC',3
UNION ALL SELECT 'BCD',1  
UNION ALL SELECT 'CDE',2  
UNION ALL SELECT 'DEF',1;

WITH x AS 
(
  SELECT TOP (10) rn = ROW_NUMBER() --since OP stated max = 10
  OVER (ORDER BY [object_id]) 
  FROM sys.all_columns 
  ORDER BY [object_id]
)
SELECT d.Supplier, d.Quantity
FROM x
CROSS JOIN @d AS d
WHERE x.rn <= d.Quantity
ORDER BY d.Supplier;