Get custom product selection upon dynamic ID in SQL

This looks like two levels of aggregation to me:

select col1,
       sum(r_coke) as r_coke,
       sum(r_fant) as r_fant,
       sum(arwa) as arwa,
       sum(r_sprt) as r_sprt
from (select max(case when level = 0 then description end) as col1,
             sum(case when description = 'R-COKE' then 1 else 0 end) as r_coke,
             sum(case when description = 'R-FANT' then 1 else 0 end) as r_fant,
             sum(case when description = 'ARWA' then 1 else 0 end) as arwa,
             sum(case when description = 'R-SPRT' then 1 else 0 end) as r_sprt
      from t
      group by id
     ) x
group by col1;

Or, perhaps more simply, using window functions:

select col1,
       sum(case when description = 'R-COKE' then 1 else 0 end) as r_coke,
       sum(case when description = 'R-FANT' then 1 else 0 end) as r_fant,
       sum(case when description = 'ARWA' then 1 else 0 end) as arwa,
       sum(case when description = 'R-SPRT' then 1 else 0 end) as r_sprt
from (select t.*,
             max(case when level = 0 then description end) over (partition by id) as col1
      from t
     ) t
group by col1;

Just following input and output provided

select orderID, x, [R-COKE], [R-FANT], [ARWA], [R-SPRT]
from (
   select orderID, id
    , max(case when level = 0 then description end) x
    , max(case when level = 1 and solvedChoice is not null then description end) y
   from mytable
   group by orderID, id
) t
pivot (count(id) for y in ([R-COKE], [R-FANT], [ARWA], [R-SPRT]) ) pvt;

You could join the table to itself. Something like this

drop TABLE if exists #MyItemTable;
go
CREATE TABLE #MyItemTable 
(
    orderID INT,
    code int,
    action char(6),
    id int,
    level int,
    description varchar(10),
    price money,
    solvedChoice int
)
INSERT INTO #MyItemTable (orderID, code,    action,  id , level,   description, Price,       solvedChoice)
VALUES 
 (321, 622 ,'RECIPE',0,0,'SPICM1',15.5 ,NULL)
,(321, 10  ,'RECIPE',0,1,'SPICKN',17   ,NULL    )
,(321, 7091,'RECIPE',0,1,'RFRY  ',8.5  ,NULL    )
,(321, 521 ,'CHOICE',0,1,'R-COKE',7.5  ,10000003)
,(321, 612 ,'RECIPE',1,0,'BIGTM1',20.5 ,NULL    )
,(321, 13  ,'RECIPE',1,1,'BTASTY',21   ,NULL    )
,(321, 7091,'RECIPE',1,1,'RFRY  ',8.5  ,NULL    )
,(321, 522 ,'CHOICE',1,1,'R-FANT',7.5  ,10000003)
,(321, 608 ,'RECIPE',2,0,'ROYAL1',18.5 ,NULL    )
,(321, 11  ,'RECIPE',2,1,'MCROYA',18   ,NULL    )
,(321, 7091,'RECIPE',2,1,'RFRY  ',8.5  ,NULL    )
,(321, 411 ,'CHOICE',2,1,'ARWA  ',7.5  ,10000003)
,(321, 612 ,'RECIPE',3,0,'BIGTM1',20.5 ,NULL    )
,(321, 13  ,'RECIPE',3,1,'BTASTY',21   ,NULL    )
,(321, 7091,'RECIPE',3,1,'RFRY  ',8.5  ,NULL    )
,(321, 524 ,'CHOICE',3,1,'R-SPRT',7.5  ,10000003);

select i.[description],
       sum(case when i2.[description] = 'R-COKE' then 1 else 0 end) as r_coke,
       sum(case when i2.[description] = 'R-FANT' then 1 else 0 end) as r_fant,
       sum(case when i2.[description] = 'ARWA' then 1 else 0 end) as arwa,
       sum(case when i2.[description] = 'R-SPRT' then 1 else 0 end) as r_sprt
from #MyItemTable i
     left join #MyItemTable i2 on i.id=i2.id
                                  and i2.[action]='CHOICE'
where i.[level]=0
group by i.[description];

description r_coke  r_fant  arwa    r_sprt
BIGTM1      0       1       0       1
ROYAL1      0       0       1       0
SPICM1      1       0       0       0

The aim is to get 1 result for each type of order, that is represented by level=0, id is the order identifier.

You need to first normalize the results by querying the orders and the CHOICE items separately, then you can correlate them with a join.

Once you have identified the spearate order and item records, then we can easily target them with aggregates, in this case a simple COUNT

COUNT works well in this context because it will exclude NULL values.

SELECT [order].description
    , COUNT(DISTINCT [order].id) as [Orders]
    , COUNT(CASE WHEN item.description = 'R-COKE' THEN 1 END) as [R-COKE]
    , COUNT(CASE WHEN item.description = 'R-FANT' THEN 1 END) as [R-FANT]
    , COUNT(CASE WHEN item.description = 'ARWA' THEN 1 END) as [ARWA]
    , COUNT(CASE WHEN item.description = 'R-SPRT' THEN 1 END) as [R-SPRT]
FROM tblOrders [order]
INNER JOIN tblOrders item ON item.id = [order].id AND item.level = 1
WHERE [order].level = 0
GROUP BY [order].description

Try it out in this fiddle: http://sqlfiddle.com/#!18/81ec5/1

To further highlight the groupings, I have included a count of the separate orders, seeing we are counting the drinks as well.