I am trying to write a query. I have 2 table in first table customer and 2018, 2019 order and profit data. In second table customer and 2020 0rder and profit data.

I want to write a query that will return all customer and their order and profit in a different format.

I have attached the screenshot of data and output.

CodePudding user response：

use full outer join

select
coalesce(t1.customer,t2.customer) as customer,
t1.order2018.t1.profit2018,t1.order2019,t1.profit2019,
t2.order2020,t2.profit2020
 from table1 as t1 full outer join table2 t2
on t1.cutomer=t2.customer