mysql - 使用 mysql 查询将数据显示为列标题

Question

我的数据看起来像

EmployeeId      paycategory         value

  1             Contribution         200
  1              Salary              18000

我想将结果显示为，

EmployeeId        Salary      Contribution
      1             18000           200

我的尝试看起来像，

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'

结果显示为，

 EmployeeId      paycategory        value

  1             Contribution         200
  1              Salary              18000

与原始数据格式相同

标签： mysqlsqlstringpivotunion

Answer 1

使用条件聚合来透视您的数据集：

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