>Hi Cetin,
>
>>In SQL2005 AdventureWorks database check stored procedures. If I don't remember wrong one the procedures was almost exactly this, creating a bike using CTE with recursion.
>>
>>PS: Looks like I remember right:)
>>uspGetBillOfMaterials
>>uspGetWhereUsedProductID
>
>Thx. I'll take a look at that. I actually found several examples on the web (the most useful one, as usual, *after* I struggled to a solution).
>
>I've still got one problem though- see my post to Sergey...
>Regards,
>Viv

Viv,
Probably I don't understand what you mean else this fits your sample:

WITH Parts 
(id,recipeid,quan,itemid,itemisrecipe,portion,_level) AS
(
-- Anchor
SELECT 
  id,recipeid,quan,itemid,
  itemisrecipe,portion,0 as _level
  FROM TestR 
  WHERE (recipeid = 23)
UNION ALL
-- Recursive
SELECT
  r.id,r.recipeid,r.quan,r.itemid,
  r.itemisrecipe,r.portion,_level + 1
FROM TestR r 
  INNER JOIN Parts p ON p.itemid = r.recipeid and p.itemisrecipe=1
)
select id,recipeid,quan,itemid,itemisrecipe,portion,_level from parts

Cetin