MySQL – Query and group in a single row

I have a set of data as below:

EmployeeId	Salary_Basic	Salary_Transport	Year
E001	12000	3000	2018
E002	9000	2000	2018
E001	13000	3200	2019
E002	10000	2400	2019
E003	15000	5000	2019

What I want is below:

EmployeeID	NetSalary_Year2018	NetSalary_Year2019
E001	15000	16200
E002	11000	12400
E003	0	20000

Can anyone suggest the sql query on MySQL please?

Thanks

Answer

As @Akina says, use “conditional aggregate”.

For example:

select
  employeeid,
  sum(case when year = 2018 then salary_basic + salary_transport else 0 end) as net_salary_2018,
  sum(case when year = 2019 then salary_basic + salary_transport else 0 end) as net_salary_2019
from t
group by employeeid