sql add rows based on column values-CodePudding

In sql, I have a table that looks like this:

Status	Days
A	0
B	3
C	7

And would like to create a table like this:

Status	Days
A	0
A	1
A	2
B	3
B	4
B	5
B	6
C	7
Null	8
Null	9
Null	10

Note the column Days stop at 10 as it should be a predefined value.

CodePudding user response：

SQL Server

Maybe not the most elegant solution, but you can use a query like this

WITH d(days) AS (
     SELECT 0
     UNION ALL
     SELECT days 1 FROM d WHERE days < 10
)
, cte AS (
    SELECT 
        status,
        days,
        LEAD(days) OVER (ORDER BY status) AS lead_days
    FROM data
    UNION ALL
    SELECT status, days   1, lead_days FROM cte WHERE days   1 < lead_days
)
SELECT status, d.days INTO new_table FROM cte
RIGHT JOIN d ON d.days = cte.days
ORDER BY days

Please, check a demo

CodePudding user response：

A simplified version of Alexey's recursive solution

WITH cte AS (
    SELECT 
        status,
        days,
        LEAD(days, 1, 11) OVER (ORDER BY status) AS lead_days
    FROM data
    UNION ALL
    SELECT case lead_days when 11 then null else status end status, days   1, lead_days 
    FROM cte 
    WHERE days   1 < lead_days
)
SELECT status, days
FROM cte
ORDER BY days