title: SQL convert rows to columns with static headers
body: <p>I have a data set that I need to transform from rows to columns.</p>
  
  <p>I looked at pivot and does not really answer the problem since I need specific static column headers (not matching the data names).</p>
  
  <p>Here is how the current data is stored:</p>
  
  <pre><code>Id | SpCode    
  ------------------------- 
  1    AA  
  1    BB    
  1    CC    
  2    FF    
  2    LL    
  3    TT    
  3    YY    
  3    ZZ    
  3    XX    
  4    AA    
  4    BB        
  5    AA    
  6    WW    
  6    MM    
  6    PP   
  </code></pre>
  
  <p>I need to display it like this.</p>
  
  <p>I will never have more than 5 columns and would like to keep the column name static so I can join and refer to it easily.</p>
  
  <pre><code>ID  SpCode1 |SpCode2 | SpCode3 | SpCode4 | SpCode5
  1   AA      |BB      | CC       
  2   FF      |LL         
  3   TT      |YY      |ZZ       |XX  
  4   AA      |BB                 
  5   AA              
  6   WW      |MM      |PP        
  </code></pre>
  
  <p>Much appreciated for any help.</p>
  
  <p>PS: using SQL Server 2014</p>
  
  <p>Thanks</p>
  
  <p>Oren</p>