database - how can I aggregation values with all the values from a column in MySQL

Question

If I have a table below:

company | year |quarter| revenue
A          2019   1       200
A          2019   3       100
B          2019   2       80
B          2019   4       40

How can I get the result displayed as:

year |quarter| company |revenue
2019   1        A
2019    2       A
2019    3       A
2019    4       A
2019    1       B
...     ...     B

Answer 1

A cross join should do

with cte as
(
select distinct company,year from t
)
select cte.year,s.qtr,cte.company
from cte
cross join (select 1 as qtr union select 2 union select 3 union select 4) s 
order by cte.company,s.qtr;