Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (
  id int
, extra_info varchar(3)
, month text
, date date
, value int
);
   
INSERT INTO tbl VALUES
  (1, 'abc', 'jan', '2012-01-01', 10)
, (1, 'abc', 'feb', '2012-02-01', 20)
, (2, 'def', 'jan', '2012-01-01', 10)
, (2, 'def', 'feb', '2012-02-01',  5)
, (1, 'abc', 'jan', '2012-01-01', 15)
, (3, 'ghi', 'mar', '2012-03-01', 15)
;

CREATE TABLE



INSERT 0 6


SELECT * FROM crosstab(
   $$
   SELECT id, min(extra_info), month, sum(value) AS value
   FROM   tbl
   GROUP  BY id, month
   ORDER  BY id, month
   $$
 , $$
   VALUES
     ('jan'::text), ('feb'), ('mar'), ('apr'), ('may'), ('jun')
   , ('jul'),       ('aug'), ('sep'), ('oct'), ('nov'), ('dec')
   $$
   ) AS ct (id  int, extra text
          , jan int, feb int, mar int, apr int, may int, jun int
          , jul int, aug int, sep int, oct int, nov int, dec int);


id
extra
jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec


1
abc
25
20
null
null
null
null
null
null
null
null
null
null

2
def
10
5
null
null
null
null
null
null
null
null
null
null

3
ghi
null
null
15
null
null
null
null
null
null
null
null
null



SELECT 3


-- 0 instead of NULL
SELECT id, extra
     , COALESCE(jan, 0) AS jan
     , COALESCE(feb, 0) AS feb
     , COALESCE(mar, 0) AS mar
     , COALESCE(apr, 0) AS apr
     , COALESCE(may, 0) AS may
     , COALESCE(jun, 0) AS jun
     , COALESCE(jul, 0) AS jul
     , COALESCE(aug, 0) AS aug
     , COALESCE(sep, 0) AS sep
     , COALESCE(oct, 0) AS oct
     , COALESCE(nov, 0) AS nov
     , COALESCE(dec, 0) AS dec
FROM   crosstab(
   $$
   SELECT id, min(extra_info), month, sum(value) AS value
   FROM   tbl
   GROUP  BY id, month
   ORDER  BY id, month
   $$
 , $$
   VALUES
     ('jan'::text), ('feb'), ('mar'), ('apr'), ('may'), ('jun')
   , ('jul'),       ('aug'), ('sep'), ('oct'), ('nov'), ('dec')
   $$
   ) AS ct (id  int, extra text
          , jan int, feb int, mar int, apr int, may int, jun int
          , jul int, aug int, sep int, oct int, nov int, dec int);


id
extra
jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec


1
abc
25
20
0
0
0
0
0
0
0
0
0
0

2
def
10
5
0
0
0
0
0
0
0
0
0
0

3
ghi
0
0
15
0
0
0
0
0
0
0
0
0



SELECT 3


SELECT id, min(extra_info) AS extra
     , sum(value) FILTER (WHERE month = 'jan') AS jan
     , sum(value) FILTER (WHERE month = 'feb') AS feb
     , sum(value) FILTER (WHERE month = 'mar') AS mar
     , sum(value) FILTER (WHERE month = 'apr') AS apr
     , sum(value) FILTER (WHERE month = 'may') AS may
     , sum(value) FILTER (WHERE month = 'jun') AS jun
     , sum(value) FILTER (WHERE month = 'jul') AS jul
     , sum(value) FILTER (WHERE month = 'aug') AS aug
     , sum(value) FILTER (WHERE month = 'sep') AS sep
     , sum(value) FILTER (WHERE month = 'oct') AS oct
     , sum(value) FILTER (WHERE month = 'nov') AS nov
     , sum(value) FILTER (WHERE month = 'dec') AS dec
FROM   tbl
GROUP  BY id
ORDER  BY id;


id
extra
jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec


1
abc
25
20
null
null
null
null
null
null
null
null
null
null

2
def
10
5
null
null
null
null
null
null
null
null
null
null

3
ghi
null
null
15
null
null
null
null
null
null
null
null
null



SELECT 3

id	extra	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
1	abc	25	20	null	null	null	null	null	null	null	null	null	null
2	def	10	5	null	null	null	null	null	null	null	null	null	null
3	ghi	null	null	15	null	null	null	null	null	null	null	null	null

id	extra	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
1	abc	25	20	null	null	null	null	null	null	null	null	null	null
2	def	10	5	null	null	null	null	null	null	null	null	null	null
3	ghi	null	null	15	null	null	null	null	null	null	null	null	null