Recursive CTE Sample


  


 create 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

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s