如何用不同的输入选择相同的列

Question

案例是，我想做一份报告，使用SSRS来比较两年之间的员工工资。年份、员工数量和工资在1个表中。

表结构为:例如：his_pay_period_num, his_employee_num, Salary：

薪水

2007,A1234,$30000
2009,A1234,$50000
2009,B1234,$15000

我想输出：

A1234 , $30000, $50000   <-----how can I select 2 Salary with different input(2007,2009)?
B1234,  $0, $15000  <----------- And if no Record,return 0

目前，我正在做以下工作：

SELECT  t1.his_pay_period_num, t1.his_employee_num ,t1.Salary AS rate1,
  t2.Salary AS rate2
FROM          [Salary] as t1 Left outer join
            [Salary] as t2 on t1.id = t2.id
WHERE        (t1.his_pay_period_num = '2007') or  (t2.his_pay_period_num= '2009')

它输出：

A1234, $30000,$30000
A1234, $50000,$50000   <-----just display 2 record 
B1234, $15000,$15000   

问题1:如何选择2个不同输入的薪资？

问题2:如果不存在记录，则返回0

Answer 1

假设您的数据库管理系统支持PIVOT关键字，您可以这样做：

select 
his_employee_num, 
coalesce([2007],0) as [Pay in 2007], --0 if first argument is NULL
coalesce([2009],0) as [Pay in 2009]
from
(select * from salary) s
pivot
(max(salary) for his_pay_period in ([2007],[2009])) p 

这将适用于SQL Server和Oracle (可能需要进行一些调整)。