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')) AS sale_date
FROM generate_series(1,10000) 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 ) 
;
-- /** 
-- END test data 
-- END test data 
-- END test data 
--  */

-- /** 
-- dyn. build a temporary row-type based on existing data, not hard-coded
-- dyn. build a temporary row-type based on existing data, not hard-coded
-- dyn. build a temporary row-type 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 $$;

DROP TABLE IF EXISTS tmpMoToJson ;
CREATE TEMP TABLE tmpMoToJson AS
	  SELECT 
	     year AS year
	     ,(json_build_array( months )) AS js_months_arr
	     ,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  
  year
,(ROW((jprs).*)::tmpTblTyp).* -- explode the composite type row
FROM tmpMoToJson ;

year	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
2010	124	103	122	127	119	126	113	113	116	110	104	125
2011	130	108	133	123	135	111	104	138	140	133	103	109
2012	130	147	110	119	121	86	102	120	112	124	112	119
2013	145	113	120	105	124	107	109	129	118	114	108	120
2014	122	125	118	107	119	113	123	125	118	116	103	126
2015	122	110	123	134	126	124	120	108	110	127	113	123
2016	107	98	120	127	118	137	109	123	124	127	146	126

SELECT 7