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.
2 Answers
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
1 Comment
mcadorel
Got something similar on postgresql : db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/5359
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
1 Comment
Rafa
thanks for your solution but Alexey was faster and that is why I marked his response. I suggest updating Alexey response with your simplified query.
INSERT ... SELECTstatement to insert values based upon what you select, but I don't believe you can use that to insert an arbitrarily sized range down to zero.