I have this table:
| product_id | period_start | period_end |
|---|---|---|
| 1 | 2019-01-25 | 2019-02-28 |
| 2 | 2018-12-01 | 2020-01-01 |
| 3 | 2019-12-01 | 2020-01-31 |
I want:
| product_id | year |
|---|---|
| 1 | 2019 |
| 2 | 2018 |
| 2 | 2019 |
| 2 | 2020 |
| 3 | 2019 |
| 3 | 2020 |
How can I get one row for each product_id and year combination in SQL?
CodePudding user response：
You don't want all product/year combinations. You only want the years related to a product. For this you need a recursive query.
with recursive cte (product_id, year, last_year) as
(
select product_id, year(period_start), year(period_end) from products
union all
select product_id, year 1, last_year from cte where year < last_year
)
select product_id, year
from cte
order by product_id, year;
Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7f3bb10734e2aacf99eabc7730e412eb