根据每行中的列值复制行 [英] Duplicating rows based on a column value in each row

问题描述

我的这张表包含以下数据

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

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

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.

结果表应该如下

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?

我正在使用 sql server

I'm using sql server

推荐答案

这将支持任何单个作业(在我的系统上)超过 7,400 次重复.如果需要更多，可以使用不同的系统表或交叉联接.

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.

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;

这篇关于根据每行中的列值复制行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！