Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE invoices (id serial PRIMARY KEY, year integer, creation_date date, marking text);

INSERT INTO invoices (year, creation_date, marking) VALUES
(2020, '20200101', 'Delivered'),
(2020, '20200101', 'Not Delivered'),
(2020, '20200101', 'Not Received'),
(2020, '20200301', 'Delivered'),
(2020, '20200301', 'Delivered'),
(2020, '20200301', 'Not Delivered'),
(2020, '20200301', 'Not Delivered'),
(2021, '20210301', 'Delivered'),
(2021, '20210101', 'Delivered'),
(2021, '20210101', 'Not Received'),
(2021, '20210101', 'Not Delivered'),
(2021, '20210101', 'Not Delivered'),
(2021, '20210201', 'Not Received'),
(2021, '20210801', 'Delivered'),
(2021, '20210801', 'Not Delivered'),
(2021, '20210801', 'Not Received'),
(2021, '20210801', 'Not Received'),
(2022, '20220801', 'Delivered'),
(2022, '20220801', 'Delivered'),
(2022, '20221101', 'Not Delivered'),
(2022, '20221101', 'Delivered'),
(2022, '20221101', 'Delivered'),
(2022, '20221101', 'Not Delivered'),
(2022, '20221101', 'Not Delivered'),
(2022, '20221101', 'Not Received'),
(2022, '20221201', 'Not Received'),
(2022, '20221201', 'Not Received');

CREATE TABLE



INSERT 0 27


-- query to generate SQL dynamically
SELECT format(
$f$SELECT * FROM crosstab(
   $q$
   SELECT to_char(date_trunc('month', creation_date), 'YYYY_Month') AS year_month
        , marking
        , COUNT(*) AS ct
   FROM   invoices
   GROUP  BY date_trunc('month', creation_date), marking
   ORDER  BY date_trunc('month', creation_date)  -- optional
   $q$
 , $c$VALUES (%s)$c$
   ) AS ct(year_month text, %s);
$f$, string_agg(quote_literal(sub.marking), '), (')
   , string_agg(quote_ident  (sub.marking), ' int, ') || ' int'
)
FROM  (SELECT DISTINCT marking FROM invoices ORDER BY 1) sub;


format


SELECT * FROM crosstab(
   $q$
   SELECT to_char(date_trunc('month', creation_date), 'YYYY_Month') AS year_month
        , marking
        , COUNT(*) AS ct
   FROM   invoices
   GROUP  BY date_trunc('month', creation_date), marking
   ORDER  BY date_trunc('month', creation_date)  -- optional
   $q$
 , $c$VALUES ('Delivered'), ('Not Delivered'), ('Not Received')$c$
   ) AS ct(year_month text, "Delivered" int, "Not Delivered" int, "Not Received" int);




SELECT 1


-- execute generated query
SELECT * FROM crosstab(
   $q$
   SELECT to_char(date_trunc('month', creation_date), 'YYYY_Month') AS year_month
        , marking
        , COUNT(*) AS ct
   FROM   invoices
   GROUP  BY date_trunc('month', creation_date), marking
   ORDER  BY date_trunc('month', creation_date)  -- optional
   $q$
 , $c$VALUES ('Delivered'), ('Not Delivered'), ('Not Received')$c$
   ) AS ct(year_month text, "Delivered" int, "Not Delivered" int, "Not Received" int);


year_month
Delivered
Not Delivered
Not Received


2020_January
1
1
1

2020_March
2
2
null

2021_January
1
2
1

2021_February
null
null
1

2021_March
1
null
null

2021_August
1
1
2

2022_August
2
null
null

2022_November
2
3
1

2022_December
null
null
2



SELECT 9
year_month	Delivered	Not Delivered	Not Received
2020_January	1	1	1
2020_March	2	2	null
2021_January	1	2	1
2021_February	null	null	1
2021_March	1	null	null
2021_August	1	1	2
2022_August	2	null	null
2022_November	2	3	1
2022_December	null	null	2