Postgres 9.4

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

-- /** 
-- begin test data 
--  */
DROP TABLE IF EXISTS tmpSales ;
CREATE TEMP TABLE tmpSales AS
SELECT
  sale_id
  ,TRUNC(RANDOM()*12)+1 AS book_id
  ,TRUNC(RANDOM()*100)+1 AS customer_id
  ,(date '2010-01-01' + random() * (timestamp '2016-12-31' - timestamp '2010-01-01'))::DATE AS sale_date
FROM generate_series(1,1000) AS sale_id;
 

DROP TABLE IF EXISTS tmp_month_total ; 
CREATE TEMP TABLE tmp_month_total AS 
  SELECT
     date_part( 'year' , sale_date ) AS year
    ,date_part( 'month', sale_date ) AS mn 
    ,to_char(sale_date, 'mon') AS month
    ,COUNT(*) AS total
  FROM tmpSales
  GROUP BY date_part('year', sale_date), to_char(sale_date, 'mon') ,date_part( 'month', sale_date ) 
;

SELECT * FROM tmp_month_total LIMIT 15 ;
 


year
mn
month
total


2016
4
apr
15

2010
4
apr
11

2014
6
jun
7

2011
6
jun
19

2012
11
nov
8

2012
8
aug
9

2013
4
apr
15

2014
2
feb
19

2011
7
jul
14

2012
5
may
20

2011
2
feb
10

2012
3
mar
10

2014
7
jul
6

2015
1
jan
9

2012
4
apr
16



SELECT 15


-- /** 
-- using an anon. proc. to define the column headers
-- of a temporary record-type (as temp table)
-- based on existing data
--  **/
DROP TABLE IF EXISTS tmpTblTyp CASCADE ; 
DO LANGUAGE plpgsql $$ DECLARE v_sqlstring VARCHAR  = ''; BEGIN 
v_sqlstring := CONCAT( 'CREATE TEMP TABLE tmpTblTyp AS SELECT '   
                       ,(SELECT  STRING_AGG( CONCAT('NULL::int AS ' , month )::TEXT , ' ,' 
                            ORDER BY mn                        
                           )::TEXT
                                  || ' , NULL::int AS undec ' -- added these to show flexibility of json. 
                                  || ' , NULL::int AS dodec ' -- the actual data lacks these two extra columns
 
                           FROM
                           (SELECT DISTINCT month, mn FROM tmp_month_total )a )
                       ,' LIMIT 0 '    
                       ) ; -- RAISE NOTICE '%', v_sqlstring ;  
EXECUTE( v_sqlstring ) ; END $$; 

-- show our empty table struct
SELECT * FROM tmpTblTyp ; 
 


jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec
undec
dodec



SELECT 0


-- /**
-- build the JSON record-set using the dyn. rec-type
--**/
DROP TABLE IF EXISTS tmpMoToJson ;
CREATE TEMP TABLE tmpMoToJson AS
	  SELECT 
	     year AS year
	     ,json_populate_recordset ( NULL::tmpTblTyp /** use temp table (above) as a record type definition!! **/
	                              , json_build_array( months ) 
	                              ) jprs /** builds row-type column that can be expanded with " (jprs).* "    **/   
FROM ( SELECT year
      -- accum & pivot data into JSON array
	  ,json_object_agg(month,total) AS months 
	   FROM tmp_month_total
	   GROUP BY year
	   ORDER BY year
	 ) a
; 



SELECT 7


SELECT  
year
,(jprs).* -- expand rowtype into columns
FROM tmpMoToJson ;


year
jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec
undec
dodec


2010
15
10
14
11
13
11
17
9
22
11
6
8
null
null

2011
13
10
7
16
16
19
14
10
6
12
11
13
null
null

2012
11
13
10
16
20
16
10
9
12
4
8
6
null
null

2013
10
9
10
15
23
9
15
14
12
12
13
12
null
null

2014
13
19
12
11
11
7
6
7
11
7
13
7
null
null

2015
9
15
7
10
9
14
16
18
6
14
12
15
null
null

2016
6
12
12
15
17
8
12
14
13
12
12
15
null
null



SELECT 7

year	mn	month	total
2016	4	apr	15
2010	4	apr	11
2014	6	jun	7
2011	6	jun	19
2012	11	nov	8
2012	8	aug	9
2013	4	apr	15
2014	2	feb	19
2011	7	jul	14
2012	5	may	20
2011	2	feb	10
2012	3	mar	10
2014	7	jul	6
2015	1	jan	9
2012	4	apr	16

year	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec	undec	dodec
2010	15	10	14	11	13	11	17	9	22	11	6	8	null	null
2011	13	10	7	16	16	19	14	10	6	12	11	13	null	null
2012	11	13	10	16	20	16	10	9	12	4	8	6	null	null
2013	10	9	10	15	23	9	15	14	12	12	13	12	null	null
2014	13	19	12	11	11	7	6	7	11	7	13	7	null	null
2015	9	15	7	10	9	14	16	18	6	14	12	15	null	null
2016	6	12	12	15	17	8	12	14	13	12	12	15	null	null