CODEWITHSUNDEEP
sql-server
Kelvin
Kelvin

Reputation: 59

Is it possible to duplicate a row based on a column value, and change another column value?

I have a table with an INT column of time in seconds, another column with the type of that record, and a foreign ID.

I want to select the same row repeated everytime it's seconds row is greater than X, and in the first row it keeps X seconds with type 1, and in the second row it shows the seconds left (seconds - X, up to X) with type 2. The same with Y. No more than 3 rows. So row 1 is up to x, row 2 up to Y and row 3 is Y+1 and beyond

Eg:

X is 5. Y is 9. I want this:

| id | type | seconds |
|----|------|---------|
|  1 |  10  |   19    |
|  2 |  10  |   12    |
|  3 |  10  |    7    |

to become this:

| id | type | seconds |
|----|------|---------|
|  1 |   1  |    5    |
|  1 |   2  |    4    |
|  1 |   3  |   10    |
|  2 |   1  |    5    |
|  2 |   2  |    4    |
|  2 |   3  |    3    |
|  3 |   1  |    5    |
|  3 |   2  |    2    |

Is that possible?

I've seen solutions to this (syntax-) exclusively for oracle. But how do I do this in SQL Server?

Edit: Only for this type id (10 in the example), letting others unchanged.

Upvotes: 0

Views: 892

Answers (2)

Chris Albert
Chris Albert

Reputation: 2507

For this type of problem I like to use a recursive CTE. Borrowed the extra test cases from @Luis Cazares. Also added test cases for type 15 you mentioned in comments. In the solution below you can swap out values of X and Y and it will calculate accordingly.

CREATE TABLE #TestData
(
    id INT
    , type INT
    , seconds int
);

INSERT INTO #TestData VALUES
(1, 10, 19)
, (2, 10, 12)
, (3, 10, 7)
, (4, 10, 5)
, (5, 10, 3)
, (6, 15, 54)
, (7, 15, 8);

DECLARE 
    @X INT = 5
    , @Y INT = 9;

SET @Y = @Y - @X;

WITH CTE AS
(
    SELECT id, CASE WHEN type = 10 THEN 0 ELSE type END AS type, seconds, seconds AS ov 
    FROM #TestData
    UNION ALL
    SELECT 
        id
        , type + 1
        , CASE
            WHEN type = 0 THEN seconds - @X
            WHEN type = 1 THEN seconds - @Y
            WHEN type = 2 THEN seconds
            END
        , ov
    FROM CTE
    WHERE seconds > 0
)

SELECT
    id
    , type
    , CASE
        WHEN type = 1 AND seconds > 0 THEN @X
        WHEN type = 2 AND seconds > 0 THEN @Y
        WHEN type = 2 AND seconds < 0 THEN ov - @X
        WHEN type = 3 AND seconds > 0 THEN seconds
        ELSE ov
        END
FROM CTE
WHERE type <> 0 AND CTE.seconds IS NOT NULL
ORDER BY id, type

DROP TABLE #TestData;

Upvotes: 0

Luis Cazares
Luis Cazares

Reputation: 3585

This is not very pretty, but does the trick. To duplicate rows, we use tally tables. In this case I used a hard coded one with only 3 rows. Then made the calculations based on it. There might be a simpler way to code it, but I'm not on my best today.

--Creating sample data
CREATE TABLE SampleData(
    id      int,
    [type]  int,
    seconds int
);

INSERT INTO SampleData
VALUES
( 1, 10, 19),
( 2, 10, 12),
( 3, 10,  7),
( 4, 10, 5),
( 5, 10,  3);
GO

--Actual solution
DECLARE @X int = 5;

WITH CTE AS(
    SELECT id,
        CASE WHEN [type] =  10 THEN n ELSE [type] END AS [type],
        CASE WHEN [type] <> 10 THEN seconds
             WHEN n = 1 AND seconds > @X THEN @X
             WHEN n = 1 AND seconds <= @X THEN seconds
             WHEN n = 2 AND seconds - @X >= @X THEN @X-1
             WHEN n = 2 AND seconds - @X > 0 THEN seconds - @X
             WHEN n = 3 AND seconds > @X*2-1 THEN seconds - (@X*2-1)
             END AS seconds
    FROM SampleData
    CROSS JOIN( VALUES(1),(2),(3))AS x(n)
    WHERE [type] = 10 OR n = 1
)
SELECT *
FROM CTE
WHERE seconds IS NOT NULL;

Upvotes: 1

Related Questions