Postgres 11

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

create table customers (id int, customer varchar(30));
create table quarters (id int, quarter varchar(30));
create table salesAggs (id int, customer varchar(30), quarter varchar(30), amount int);

insert into customers (id, customer) values (1, 'John'), (2, 'Mary'), (3, 'Kathy');
insert into quarters (id, quarter) values (1, 'Q1'), (2, 'Q2'), (3, 'Q3'), (4, 'Q4');
insert into salesAggs (id, customer, quarter, amount)
values
    (1, 'John', 'Q1', 10),
    (2, 'John', 'Q3', 10),
    (3, 'Mary', 'Q2', 10),
    (4, 'Kathy', 'Q1', 10)
;

CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 4



INSERT 0 4


select c.id, c.customer, q.quarter, sa.amount
from (select distinct id, customer from salesAggs
     ) c cross join 
     (values ('Q1'), ('Q2'), ('Q3'), ('Q4')
     ) q(quarter) left join 
     salesAggs sa
     on q.quarter = sa.quarter and c.id = sa.id
order by customer, quarter;


id
customer
quarter
amount


1
John
Q1
10

2
John
Q1
null

2
John
Q2
null

1
John
Q2
null

1
John
Q3
null

2
John
Q3
10

1
John
Q4
null

2
John
Q4
null

4
Kathy
Q1
10

4
Kathy
Q2
null

4
Kathy
Q3
null

4
Kathy
Q4
null

3
Mary
Q1
null

3
Mary
Q2
10

3
Mary
Q3
null

3
Mary
Q4
null



SELECT 16


select c.id, c.customer, q.quarter, sa.amount
from (select distinct id, customer from salesAggs
     ) c cross join 
     (select 'Q1' as quarter union all
      select 'Q2' as quarter union all
      select 'Q3' as quarter union all
      select 'Q4' as quarter 
     ) q left join 
     salesAggs sa
     on q.quarter = sa.quarter and c.id = sa.id
order by customer, quarter;


id
customer
quarter
amount


1
John
Q1
10

2
John
Q1
null

2
John
Q2
null

1
John
Q2
null

1
John
Q3
null

2
John
Q3
10

1
John
Q4
null

2
John
Q4
null

4
Kathy
Q1
10

4
Kathy
Q2
null

4
Kathy
Q3
null

4
Kathy
Q4
null

3
Mary
Q1
null

3
Mary
Q2
10

3
Mary
Q3
null

3
Mary
Q4
null



SELECT 16

id	customer	quarter	amount
1	John	Q1	10
2	John	Q1	null
2	John	Q2	null
1	John	Q2	null
1	John	Q3	null
2	John	Q3	10
1	John	Q4	null
2	John	Q4	null
4	Kathy	Q1	10
4	Kathy	Q2	null
4	Kathy	Q3	null
4	Kathy	Q4	null
3	Mary	Q1	null
3	Mary	Q2	10
3	Mary	Q3	null
3	Mary	Q4	null