title: SQL server Pivot on Multiple Columns
body: <p>I am trying to pivot on multiple columns. I am using SQL server 2008. Here is what I have tried so far</p>
  
  <pre><code>CREATE TABLE #t ( id int, Rscd varchar(10),Accd varchar(10),position int)
  
  INSERT INTO #t Values (10,'A','B',1)
  
  INSERT INTO #t Values (10,'C','D',2)
  
  Select id,[1],[2],[11],[12] FROM
  (SELECT id, Rscd,Accd, position , position +10 as Aposition 
  From #t)
  As query
  PIVOT (MAX(Rscd )
        FOR Position IN ([1],[2])) AS Pivot1
        PIVOT (MAX(Accd )
        FOR Aposition IN ([11],[12])) AS Pivot2
  </code></pre>
  
  <p>The below indicated is the result that I am getting</p>
  
  <pre><code>id  1     2     11    12
  10  NULL  C     NULL  D
  10  A     NULL  B     NULL
  </code></pre>
  
  <p>But the result that I am trying to achieve is ,</p>
  
  <pre><code>id  1   2   11   12
  10  A   C   B    D
  </code></pre>
  
  <p>Any help ? what is wrong in my code.</p>