CODEWITHSUNDEEP
sqlpostgresqlcrosstabpostgres-crosstab
PythonSherpa
PythonSherpa

Reputation: 2600

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!

Upvotes: 1

Views: 472

Answers (2)

wildplasser
wildplasser

Reputation: 44240

Though the original question appears to be a semantic problem, the quoting of strings containing quotes might be useful for future readers:

CREATE TABLE example_table
        ( client text
        , ends_on timestamp
        );

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269683

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.

Upvotes: 1

Related Questions