Postgres 9.4

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

-- /** 
-- begin test data 
-- begin test data 
-- 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 tmpSales LIMIT 15 ;
-- /** 
-- END test data 
-- END test data 
-- END test data 
--  */


sale_id
book_id
customer_id
sale_date


1
8
85
2013-03-01

2
3
19
2010-03-16

3
2
41
2016-09-06

4
10
71
2014-07-24

5
8
99
2014-06-03

6
3
80
2010-11-08

7
11
49
2010-06-15

8
11
66
2014-10-03

9
12
89
2011-12-12

10
8
16
2014-09-06

11
12
77
2013-08-09

12
5
94
2014-11-28

13
6
3
2010-10-09

14
5
80
2015-09-07

15
1
41
2015-08-01



SELECT 15


-- /** 
-- dyn. build a temporary row-type (as temp table) based on existing data, not hard-coded
-- dyn. build a temporary row-type (as temp table) based on existing data, not hard-coded
-- dyn. build a temporary row-type (as temp table) based on existing data, not hard-coded
--  **/
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
                           FROM
                           (SELECT DISTINCT month, mn FROM tmp_month_total )a )
                       ,' LIMIT 0 '    
                       ) ; -- RAISE NOTICE '%', v_sqlstring ;  
EXECUTE( v_sqlstring ) ; END $$; 
 

DO


-- /**
-- build the JSON record-set using the dyn. row-type
-- build the JSON record-set using the dyn. row-type
-- build the JSON record-set using the dyn. row-type
--**/
DROP TABLE IF EXISTS tmpMoToJson ;
CREATE TEMP TABLE tmpMoToJson AS
	  SELECT 
	     year AS year
	     ,(json_build_array( months )) AS js_months_arr /** optional **/
	     ,json_populate_recordset ( NULL::tmpTblTyp /** use temp table as a record type!! **/
	                              , json_build_array( months ) 
	                             ) jprs /** builds row-type column that can be expanded with (jprs).*   
                                         **/
	  FROM ( SELECT year
             -- accum 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
,(ROW((jprs).*)::tmpTblTyp).* /** explode the composite type row **/
--, js_months_arr /** optional **/
FROM tmpMoToJson ;


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


2010
12
10
14
11
9
10
12
11
15
11
16
13

2011
8
15
10
12
7
9
8
16
12
14
15
12

2012
15
10
11
4
7
8
13
20
7
11
8
11

2013
11
12
12
19
9
14
9
12
17
13
13
13

2014
6
14
12
10
16
13
14
7
16
17
20
15

2015
5
11
15
19
13
7
4
10
9
9
12
10

2016
10
9
12
15
15
6
14
13
20
17
8
16



SELECT 7

sale_id	book_id	customer_id	sale_date
1	8	85	2013-03-01
2	3	19	2010-03-16
3	2	41	2016-09-06
4	10	71	2014-07-24
5	8	99	2014-06-03
6	3	80	2010-11-08
7	11	49	2010-06-15
8	11	66	2014-10-03
9	12	89	2011-12-12
10	8	16	2014-09-06
11	12	77	2013-08-09
12	5	94	2014-11-28
13	6	3	2010-10-09
14	5	80	2015-09-07
15	1	41	2015-08-01

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