根据每行中的列值复制行

I've this table with the following data

我在这张表中有以下数据

Job    Quantity Status Repeat
1      100      OK     2
2      400      HOLD   0
3      200      HOLD   1
4      450      OK     3

Based on the value in the Repeat column for each row, the row should be repeated again. For example for the Job 1, Repeat value is 2 so the Job 1 should repeat two more times.

根据每行的“重复”列中的值，应再次重复该行。例如，对于作业1，重复值为2，因此作业1应重复两次。

The resultant table should be as below

结果表应如下所示

Job    Quantity Status Repeat
1      100      OK     2
1      100      OK     2
1      100      OK     2
2      400      HOLD   0
3      200      HOLD   1
3      200      HOLD   1
4      450      OK     3
4      450      OK     3
4      450      OK     3
4      450      OK     3

Can someone please help me out with this query?

有人可以帮我解决这个问题吗？

I'm using sql server

我正在使用sql server

4 个解决方案

#1

This will support over 7,400 repeats for any individual job (on my system). If you need more, you can use a different system table or a cross join.

这将为任何个人工作（在我的系统上）支持超过7,400次重复。如果需要更多，可以使用其他系统表或交叉连接。

DECLARE @d TABLE (Job INT, Quantity INT, Status VARCHAR(12), Repeat INT);

INSERT @d SELECT 1, 100, 'OK'  ,2
UNION ALL SELECT 2, 400, 'HOLD',0
UNION ALL SELECT 3, 200, 'HOLD',1
UNION ALL SELECT 4, 450, 'OK'  ,3;

WITH x AS 
(
  SELECT TOP (SELECT MAX(Repeat)+1 FROM @d) rn = ROW_NUMBER() 
  OVER (ORDER BY [object_id]) 
  FROM sys.all_columns 
  ORDER BY [object_id]
)
SELECT * FROM x
CROSS JOIN @d AS d
WHERE x.rn <= d.Repeat + 1
ORDER BY Job;

#2

DECLARE @repeats TABLE
        (
        rn INT NOT NULL PRIMARY KEY
        );

WITH    q (rn, m) AS
        (
        SELECT  1, MAX(repeat) + 1
        FROM    jobs
        UNION ALL
        SELECT  rn + 1, m
        FROM    q
        WHERE   rn < m
        )
INSERT
INTO    @repeats
SELECT  rn
FROM    q

SELECT  j.*
FROM    jobs j
CROSS APPLY
        (
        SELECT  TOP (j.repeat + 1)
                NULL
        FROM    @repeats
        ) q (repeat)

If you have a table which guaranteedly has more records than the maximum possible value of repeat, you may get rid of @repeats and use that table instead.

如果你有一个表肯定有比重复的最大可能值更多的记录，你可以摆脱@repeats并使用该表。

#3

I prefer the below approach, as it is not reliant on external data for the query to be successful and is fairly straight forward. I've used Aaron Bertrand's code to initialize the data table but my approach on how to repeat data - this approach does not require a specific table to contain more rows than the required recursions / not dependent on external data.

我更喜欢以下方法，因为它不依赖于外部数据来使查询成功并且相当直接。我已经使用Aaron Bertrand的代码来初始化数据表，但我的方法是如何重复数据 - 这种方法不需要特定的表来包含比所需的递归更多的行/不依赖于外部数据。

DECLARE @d TABLE (Job INT, Quantity INT, Status VARCHAR(12), Repeat INT);

INSERT @d SELECT 1, 100, 'OK'  , 2
UNION ALL SELECT 2, 400, 'HOLD', 0
UNION ALL SELECT 3, 200, 'HOLD', 1
UNION ALL SELECT 4, 450, 'OK'  , 3;

DECLARE @maxRecursion INT;
SET @maxRecursion = (SELECT MAX(Repeat) 
                       FROM @d);    

WITH Iterator AS 
(
    SELECT 1 AS Iterations
    UNION ALL
    SELECT Iterations + 1 FROM Iterator WHERE Iterations < @maxRecursion
)

SELECT A.*
  FROM @d AS A
 RIGHT JOIN Iterator ON Iterator.Iterations <= (A.Repeat + 1)
 ORDER BY Job ASC
OPTION (MAXRECURSION 0)

Cheers!

干杯!

#4

-1

You may write stored procedure which will do this query (as cursor) and then you may fill new temporary table as you want

您可以编写将执行此查询的存储过程（作为游标），然后您可以根据需要填充新的临时表

CREATE FUNCTION [dbo].[GetRepeatJobs]()
RETURNS 
@JobsRepeatTable TABLE (JobId int, JobName nchar(10))
AS
BEGIN
DECLARE @i int
DECLARE @j int
DECLARE @JobId int
DECLARE @JobName nchar(10)

DECLARE JobsCursor CURSOR FOR (select JobId, JobName, JobRepeat from jobs)
OPEN JobsCursor
FETCH NEXT FROM JobsCursor INTO @JobId, @JobName, @i

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @j = 0
    WHILE @j < @i
    BEGIN
        INSERT INTO @JobsRepeatTable VALUES (@JobId, @JobName)
        SELECT @j = @j+1    
    END
    FETCH NEXT FROM JobsCursor INTO @JobId, @JobName, @i
END 
RETURN 
END

Works perfect for me.

适合我的作品。

#1