sql-server - 将根据数量复制记录的查询

Question

我正在使用 SQL Server 2008 并寻找一个将根据数量复制记录的查询

表有 QTY 和 PartNumber 列。需要为每个 QTY 添加一条附加记录。假设一个零件号的数量为 3。我需要三行包含该零件号。

任何帮助将不胜感激。谢谢。

Answer 1

测试数据

CREATE TABLE Test_Table
(
   PartNumber       VARCHAR(20) , 
   Quantity         INT, 
   [Description]    VARCHAR(20), 
   Category         VARCHAR(20),  
   Size             VARCHAR(20), 
   Tags             VARCHAR(20)
)
GO
INSERT INTO Test_Table 
VALUES
('Part 100', 3, 'Description 100', 'Cat100', 'Size100', 'Tag100'),
('Part 101', 2,'Description 101', 'Cat101', 'Size101', 'Tag101')
GO

查询

;WITH CTE (Vals) 
AS (
    SELECT 1 

     UNION ALL

    SELECT 1 + Vals 
    FROM CTE WHERE Vals < 99
   )
SELECT A.PartNumber
      , 1 AS QTY
      ,A.Category
      ,A.[Description]
      ,A.Size
      ,A.Tags
FROM       Test_Table A 
INNER JOIN CTE C   ON C.Vals <= A.Quantity
ORDER BY A.PartNumber

结果集

╔════════════╦═════╦══════════╦═════════════════╦═════════╦════════╗
║ PartNumber ║ QTY ║ Category ║   Description   ║  Size   ║  Tags  ║
╠════════════╬═════╬══════════╬═════════════════╬═════════╬════════╣
║ Part 100   ║   1 ║ Cat100   ║ Description 100 ║ Size100 ║ Tag100 ║
║ Part 100   ║   1 ║ Cat100   ║ Description 100 ║ Size100 ║ Tag100 ║
║ Part 100   ║   1 ║ Cat100   ║ Description 100 ║ Size100 ║ Tag100 ║
║ Part 101   ║   1 ║ Cat101   ║ Description 101 ║ Size101 ║ Tag101 ║
║ Part 101   ║   1 ║ Cat101   ║ Description 101 ║ Size101 ║ Tag101 ║
╚════════════╩═════╩══════════╩═════════════════╩═════════╩════════╝