title: How to select the same columns with different input
body: <p>The case is that I want to make a report using SSRS to compare the staff's Salary between 2 year.
  The years, staff number and salary is in 1 table.</p>
  
  <p>The table structure is is :
   <code>his_pay_period_num, his_employee_num, Salary</code>   for example:</p>
  
  <p>[Salary]:</p>
  
  <pre><code>2007,A1234,$30000
  2009,A1234,$50000
  2009,B1234,$15000
  </code></pre>
  
  <p>I want to output:</p>
  
  <pre><code>A1234 , $30000, $50000   &lt;-----how can I select 2 Salary with different input(2007,2009)?
  B1234,  $0, $15000  &lt;----------- And if no Record,return 0
  </code></pre>
  
  <p>Currently, I am doing:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>It outputs:</p>
  
  <pre><code>A1234, $30000,$30000
  A1234, $50000,$50000   &lt;-----just display 2 record 
  B1234, $15000,$15000   
  </code></pre>
  
  <p>Problem 1 : How can I select 2 salaries with different input?</p>
  
  <p>Problem 2 : And if no record exists, return 0</p>