[SOLVED] PostgreSQL: transpose multiple date columns dynamically to rows

PostgreSQL: transpose multiple date columns dynamically to rows

My apologies if this has been asked before. I've searched the Postgres manual and many SO answers and still can't find a solution. I'm struggling to find the right SQL (postgres) command to convert the following table:

| client | starts_on  | ends_on    |
|--------|------------|------------|
| ACME   | 2019-12-01 | 2020-02-28 |

into the desired output:

| client | year       | month      |
|--------|------------|------------|
| ACME   | 2019       | 12         |
| ACME   | 2020       | 1          |
| ACME   | 2020       | 2          |

Should this be done with crosstab? If so, how can I use the date_trunc function?

SELECT
    *
FROM
    crosstab ('SELECT client, date_trunc(' year ', ends_on), date_trunc(' month ', ends_on)
         FROM example_table') 
AS ct ("Client" text,
        "Year" int,
        "Month" int);

This throws the following error:

Query 1 ERROR: ERROR: syntax error at or near "month" LINE 4: crosstab ('SELECT client, date_trunc('month', ends_on), dat...

Any guidance would be very much appreciated!

Solution

This is not a pivoting/cross tab problem. You need to generate rows for all the months between the two dates. Use generate_series():

select t.client, extract(year from gs.dte), extract(month from gs.dte)
from t cross join lateral
     generate_series(t.starts_on, t.ends_on, interval '1 month') gs(dte);

Here is a db<>fidle.