title: sql server pivot 2 values
body: <p>I am trying to get 2 values into a single line.</p>
  
  <p>Example Data:</p>
  
  <pre><code>recipeID    componentID count
  9           21          5
  12          3           1
  12          30          1
  12          34          1
  12          96          1
  27          29          1
  27          43          1
  28          29          1
  28          44          1
  </code></pre>
  
  <p>I have tried 2 pivots, but I get </p>
  
  <pre>recipeID   1    2    3    4     11   12   13   14
  9          21   NULL NULL NULL  5    NULL NULL NULL
  12         NULL NULL NULL 96    NULL NULL NULL 1
  12         NULL NULL 34   NULL  NULL NULL 1    NULL
  12         NULL 30 NULL   NULL  NULL 1    NULL NULL
  12         3    NULL NULL NULL  1    NULL NULL NULL
  27         NULL 43 NULL   NULL  NULL 1    NULL NULL
  27         29   NULL NULL NULL  1    NULL NULL NULL
  28         NULL 44 NULL   NULL  NULL 1    NULL NULL
  28         29   NULL NULL NULL  1    NULL NULL NULL </pre>
  
  <p>When I would prefer:</p>
  
  <pre>recipeID   1    2    3    4     11   12   13   14
  9          21   NULL NULL NULL  5    NULL NULL NULL
  12         3    30   34   96    1    1    1    1
  27         29   43 NULL   NULL  1    1    NULL NULL
  28         29   44 NULL   NULL  1    1    NULL NULL</pre>
  
  <p>Any ideas?</p>
  
  <p>Current code is:</p>
  
  <pre><code>select * from (
  SELECT [recipeID]
        ,[componentID]
        ,[count]
        ,ROW_NUMBER() over(partition by [recipeID] order by ComponentID) rn
        ,ROW_NUMBER() over(partition by [recipeID] order by ComponentID)+10 rn10
  FROM [Recipe_Ingredients] ri_ ) as ri
  PIVOT
  (
  sum([componentID])
  for rn in ([1],[2],[3],[4])) as pvt
  PIVOT
  (
  sum([count])
  for rn10 in ([11],[12],[13],[14])) as pvt10
  </code></pre>