sql server pivot 2 values

Question

I am trying to get 2 values into a single line.

Example Data:

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

I have tried 2 pivots, but I get

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 

When I would prefer:

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

Any ideas?

Current code is:

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

Answer 1

Use an aggregate function (sum or max) on the columns in the outer query together with a group by as usual:

SELECT 
    recipeID
    , SUM([1]) [1]
    , SUM([2]) [2]
    , SUM([3]) [3]
    , SUM([4]) [4]
    , SUM([11]) [11]
    , SUM([12]) [12]
    , SUM([13]) [13]
    , SUM([14]) [14]
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
GROUP BY recipeID;

Sample SQL Fiddle and result:

| 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) |