CODEWITHSUNDEEP
mysqlsqlstringpivotunion
Bisoux
Bisoux

Reputation: 522

Display data as column headers using a mysql query

My data looks like

EmployeeId      paycategory         value

  1             Contribution         200
  1              Salary              18000

I want to show the results as,

EmployeeId        Salary      Contribution
      1             18000           200

My attempt looks like,

select EmployeeId,paycategory,value from Reports pi  where employeeId = 1 and paycategory = 'Salary'
 union 
  select EmployeeId,paycategory,value from Reports pi where employeeId = 1 and paycategory = 'Contribution'

The results show as,

 EmployeeId      paycategory        value

  1             Contribution         200
  1              Salary              18000

which is the same format as the original data

Upvotes: 0

Views: 73

Answers (1)

GMB
GMB

Reputation: 222462

Use conditional aggregation to pivot your dataset:

select employeeid,
    max(case when paycategory = 'Salary'       then value end) salary,
    max(case when paycategory = 'Contribution' then value end) contribution
from reports
group by employeeid

Upvotes: 2

Related Questions