CODEWITHSUNDEEP
sql-server
user1345260
user1345260

Reputation: 2249

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

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.

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

Upvotes: 10

Views: 23288

Answers (4)

Curtis Watson
Curtis Watson

Reputation: 43

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.

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!

Upvotes: 1

Alexander Kind
Alexander Kind

Reputation: 11

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.

Upvotes: -1

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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;

Upvotes: 13

Quassnoi
Quassnoi

Reputation: 425271

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.

Upvotes: 5

Related Questions