SQL Server CTE Recursion

With CTE as (
SELECT A.fnumber,a.fmaterialid as mainid,b.fmaterialid as Chileid,cast (B.fmaterialid as VARCHAR (max)) as Lev from T_eng_b Om A
JOIN dbo. T_eng_bomchild B on A.fid =b.fid
WHERE A.fid = ' 100150 '
UNION All
SELECT D.fnumber,d.mainid,d.chileid,cast (lev as varchar (max)) + CAST (d.chileid as varchar (max)) as Lev from CTE C
JOIN (SELECT aa. Fnumber,aa. Fmaterialid as MAINID,BBB. Fmaterialid as Chileid from T_eng_bom AA
JOIN dbo. T_eng_bomchild BBB on Aa.fid =bbb. FID) d on c.chileid = D.mainid
)
SELECT * from the CTE ORDER by Lev

With CTE as (

Select A,b,c from TableA

UNION ALL

Select A,b,c from CTE a

Join TableA b on a.a = b.b

)

SELECT * FROM CTE

Grammar:

[With[,n]]
::=
expression_name[(Column_name[,n])
As (
Cte_query_definition1--anchor member (i.e., initial value or first result set)
UnionAll
Cte_query_definition2--Recursive members
)

Final Portal: http://blog.csdn.net/bin_520_yan/article/details/5998349#reply

SQL Server CTE Recursion