Recursive CTE Sample
Posted: June 8, 2015 Filed under: Uncategorized Leave a commentcreate table test222(sid int, scode nvarchar(50), parentid int, sname nvarchar(50)) insert into test222 values (1, '2323', 0, 'iam a boy') insert into test222 values (2, '23231000', 1, 'boy') insert into test222 values (3, '23232', 1, 'boo') insert into test222 values (4, '232321', 3, 'bo') insert into test222 values (5, '23232110', 4, 'boyy') insert into test222 values (6, '23232190', 4, 'gril') insert into test222 values (7, '232329', 3, 'body') insert into test222 values (8, '23232910', 7, 'girll') insert into test222 values (9, '23232990', 7, 'boy') insert into test222 values (10, '23233000', 1, 'bo') insert into test222 values (11, '232390', 1, 'nh') insert into test222 values (12, '23239010', 10, 'ui') insert into test222 values (13, '23239020', 10, 'dert') insert into test222 values (14, '23239030', 10, 'hyui') insert into test222 values (15, '23239040', 10, 'nji') insert into test222 values (16, '23239090', 10, 'vfr') insert into test222 values (17, '2345', 0, 'boo') insert into test222 values (18, '23455', 17, 'bo') declare @name nvarchar(50) ='boy' declare @tmp table ( sid int primary key, Parentid int, scode nvarchar(50), sname nvarchar(50)) ;with mycte as ( select sid, scode, Parentid, sName, Cast(scode as varchar(2000)) as Hierarchy, 1 as lvl from test222 WHERE sname =@name union all select t.sid,t.scode, t.Parentid, t.sName, Cast(t.scode+'\'+m.Hierarchy as varchar(2000)) , m.lvl+1 from test222 t join mycte m on t.sid=m.Parentid ) insert into @tmp (sid, scode, Parentid, sName) Select distinct sid, scode, Parentid, sName FROM mycte ;WIth resultCTE as ( select sid, scode, Parentid, sName, Cast(scode as varchar(2000)) as Hierarchy, 1 as Level from @tmp Where parentid=0 Union all select m1.sid, m1.scode, m1.Parentid, m1.sName, Cast(m1.scode+'\'+m2.Hierarchy as varchar(2000)), m2.Level+1 from @tmp m1 join resultCTE m2 on m1.Parentid=m2.sid ) Select * from resultCTE drop table test222
http://stackoverflow.com/questions/10095032/order-of-recursion-sql-server-cte
http://forums.asp.net/t/2054555.aspx?query