title: Retrieving multiple rows using Pivot in SQL
body: <p>I am having difficulty in understanding the below SQL query to pivot the table,</p>
  
  <p>Table Name : Occupations</p>
  
  <pre><code>Name    | Occupation
  -----------------
  Sam     | Actor
  Julia   | Singer
  Ketty   | Actor
  </code></pre>
  
  <p>Expected result:</p>
  
  <pre><code>Actor   | Singer
  ----------------
  Sam     | Julia
  Ketty   | null
  </code></pre>
  
  <p>I got only one row, When I executed the below query,</p>
  
  <pre><code>Select [Actor],[Singer] From ( Select Occupation,Name From Occupations
   )sub Pivot (Max(Name) For Occupation in ([Actor],[Singer])) pvt
  </code></pre>
  
  <p>output :</p>
  
  <pre><code>Actor   | Singer
  ----------------
  Sam | Julia
  </code></pre>
  
  <p>When I modified the above query using RowNumber(), I got the expected result(multiple rows).</p>
  
  <pre><code> Select [Actor],[Singer] 
   From ( Select Occupation,Name,Row_Number() over(partition by Occupation order by Name)SNo
   From Occupations )sub
   Pivot (Max(Name) For Occupation in ([Actor],[Singer])) pvt
  </code></pre>
  
  <p>Can you explain , How adding Row_Number function in the sub query gives multiple row?</p>