Postgres 10

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

CREATE TABLE sales(
   id serial, 
   country VARCHAR(3),
   type VARCHAR(15),
   month int, 
   amount numeric(10,2)
);

INSERT INTO sales(country,type,month,amount) 
VALUES ('PEN','Course',1,16),('PEN','Course',2,12),('PEN','Course',3,24),
       ('PEN','Subscription',4,30),('PEN','Subscription',5,30),('PEN','Subscription',6,30),
       ('PEN','Course',7,16),('PEN','Subscription',8,30),('PEN','Course',9,16),
       ('PEN','Subscription',10,30),('PEN','Subscription',11,30),('PEN','Course',12,12),
       ('COL','Subscription',1,30),('COL','Course',4,24),('COL','Subscription',6,30),
       ('COL','Subscription',12,30),('BOL','Course',1,12),('BOL','Course',3,12),
       ('MXN','Course',5,16);

CREATE TABLE



INSERT 0 19


CREATE EXTENSION IF NOT EXISTS tablefunc; 

CREATE EXTENSION


SELECT *
    FROM crosstab(
      'SELECT CONCAT(country,''-'',type) AS key, month, SUM(amount) FROM sales GROUP BY key, month ORDER BY 1,2',
      'SELECT month FROM generate_series(1,12) AS month'
) AS (
    key text,
    Jan NUMERIC(10,2),
    Feb NUMERIC(10,2),
    Mar NUMERIC(10,2),
    Apr NUMERIC(10,2),
    May NUMERIC(10,2),
    Jun NUMERIC(10,2),
    Jul NUMERIC(10,2),
    Aug NUMERIC(10,2),
    Sep NUMERIC(10,2),
    Oct NUMERIC(10,2),
    Nov NUMERIC(10,2),
    Dec NUMERIC(10,2)
);


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


BOL-Course
12.00
null
12.00
null
null
null
null
null
null
null
null
null

COL-Course
null
null
null
24.00
null
null
null
null
null
null
null
null

COL-Subscription
30.00
null
null
null
null
30.00
null
null
null
null
null
30.00

MXN-Course
null
null
null
null
16.00
null
null
null
null
null
null
null

PEN-Course
16.00
12.00
24.00
null
null
null
16.00
null
16.00
null
null
12.00

PEN-Subscription
null
null
null
30.00
30.00
30.00
null
30.00
null
30.00
30.00
null



SELECT 6


SELECT split_part(key,'-',1) AS country, split_part(key,'-',2) AS type, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec 
    FROM crosstab(
      'SELECT CONCAT(country,''-'',type) AS key, month, SUM(amount) FROM sales GROUP BY key, month ORDER BY 1,2',
      'SELECT month FROM generate_series(1,12) AS month'
) AS (
    key text,
    Jan NUMERIC(10,2),
    Feb NUMERIC(10,2),
    Mar NUMERIC(10,2),
    Apr NUMERIC(10,2),
    May NUMERIC(10,2),
    Jun NUMERIC(10,2),
    Jul NUMERIC(10,2),
    Aug NUMERIC(10,2),
    Sep NUMERIC(10,2),
    Oct NUMERIC(10,2),
    Nov NUMERIC(10,2),
    Dec NUMERIC(10,2)
);


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


BOL
Course
12.00
null
12.00
null
null
null
null
null
null
null
null
null

COL
Course
null
null
null
24.00
null
null
null
null
null
null
null
null

COL
Subscription
30.00
null
null
null
null
30.00
null
null
null
null
null
30.00

MXN
Course
null
null
null
null
16.00
null
null
null
null
null
null
null

PEN
Course
16.00
12.00
24.00
null
null
null
16.00
null
16.00
null
null
12.00

PEN
Subscription
null
null
null
30.00
30.00
30.00
null
30.00
null
30.00
30.00
null



SELECT 6

key	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
BOL-Course	12.00	null	12.00	null	null	null	null	null	null	null	null	null
COL-Course	null	null	null	24.00	null	null	null	null	null	null	null	null
COL-Subscription	30.00	null	null	null	null	30.00	null	null	null	null	null	30.00
MXN-Course	null	null	null	null	16.00	null	null	null	null	null	null	null
PEN-Course	16.00	12.00	24.00	null	null	null	16.00	null	16.00	null	null	12.00
PEN-Subscription	null	null	null	30.00	30.00	30.00	null	30.00	null	30.00	30.00	null

country	type	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
BOL	Course	12.00	null	12.00	null	null	null	null	null	null	null	null	null
COL	Course	null	null	null	24.00	null	null	null	null	null	null	null	null
COL	Subscription	30.00	null	null	null	null	30.00	null	null	null	null	null	30.00
MXN	Course	null	null	null	null	16.00	null	null	null	null	null	null	null
PEN	Course	16.00	12.00	24.00	null	null	null	16.00	null	16.00	null	null	12.00
PEN	Subscription	null	null	null	30.00	30.00	30.00	null	30.00	null	30.00	30.00	null