How can we control the recursion in CTE?

Hi,

I would like to have some sort of control to recursive CTE, i.e. the query result should be generated level by level.

By default, it looks like this:

levels parentid, categoryid category name

--

1 0 1 aaa

2 1 100 bbb

2 1 101 ccc

2 1 102 ddd

3 102 200 xxx

3 102 201 yyy

4 201 300 zzz

5 300 400 qqq

3 101 210 ppp

....

How can I control the JOIN so that sql server query engine will process join level by level? the CTE I used as following:

with cte(levels, parentid, categoryid, title) as (

select 1,

parentid,

categoryid,

title

from categories

where parentid = 0

union all

select p.levels+1,

c.parentid,

c.categoryid,

c.title

from cte p join categories c on p.categoryid = c.parentid

)

select *

from cte

Thanks

Bill:

If what you want is just to sort the data by level, a simple order by will do -- something like:

declare @.categories table
( levels int,
parentId int,
categoryId int,
title varchar(10)
)
insert into @.categories
select 1,0,1,'aaa' union all
select 2,1,100,'bbb' union all
select 2,1,101,'ccc' union all
select 2,1,102,'ddd' union all
select 3,102,200,'xxx' union all
select 3,102,201,'yyy' union all
select 4,201,300,'zzz' union all
select 5,300,400,'qqq' union all
select 3,101,210,'ppp'

;with cte(levels, parentid, categoryid, title) as (
select 1,
parentid,
categoryid,
title
from @.categories
where parentid = 0
union all
select p.levels+1,
c.parentid,
c.categoryid,
c.title
from cte p join @.categories c on p.categoryid = c.parentid
)
select *
from cte order by levels, categoryId

/*
levels parentid categoryid title
-- -- -- -
1 0 1 aaa
2 1 100 bbb
2 1 101 ccc
2 1 102 ddd
3 102 200 xxx
3 102 201 yyy
3 101 210 ppp
4 201 300 zzz
5 300 400 qqq
*/

However, now that I think about it, I think you are asking a different question; hold on and I will get a second example.

|||

What do you mean "the query result should be generated level by level." There is never any guarantee what the result will look like unless you order it. What order do you want to achieve? Based on your generated level by level question, I would guess:

...

select *

from cte

order by levels

|||

My knee-jerk reaction to the question was the same as Louis' reaction. What I often find is that this question is intended to ask how do you display the data in tree order. That is a very different proposition. Is this more like what you are looking for:

;with cte(levels, parentid, categoryid, tree, title) as (
select 1,
parentid,
categoryid,
cast(str(categoryId, 11) as varchar(120)),
title
from @.categories
where parentid = 0
union all
select p.levels+1,
c.parentid,
c.categoryid,
cast(tree + '/' + str(c.categoryId, 11) as varchar(120)),
c.title
from cte p join @.categories c on p.categoryid = c.parentid
)
select levels,
parentId,
categoryId,
title,
replace(tree, ' ', '') as tree
from cte order by tree

/*
levels parentId categoryId title tree
-- -- -- - -
1 0 1 aaa 1
2 1 100 bbb 1/100
2 1 101 ccc 1/101
3 101 210 ppp 1/101/210
2 1 102 ddd 1/102
3 102 200 xxx 1/102/200
3 102 201 yyy 1/102/201
4 201 300 zzz 1/102/201/300
5 300 400 qqq 1/102/201/300/400
*/

|||

Thanks Kent/Louis,

Please refer to this thread: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=473895&SiteID=17.
Euan's codes work perfect for a small table, but mine is pretty large, I want the CTE returning me maximum 20 levels of result, I did use [OPTION (MAXRECURSION 20)] in my query, but because the statement will terminate first when it reaches maximum levels, in this case, ORDER BY won't work.

My result set looks like this:

Levels Pathid Paths

1 96 40885>96
1 98 40885>98
....
1 60532 40885>60532
2 193 40885>60532>193
2 194 40885>60532>194
....
2 60524 40885>60532>60524
3 698 40885>60532>60524>698
3 915 40885>60532>60524>915
...
3 60527 40885>60532>60524>60527
4 3068 40885>60532>60524>60527>3068
4 4022 40885>60532>60524>60527>4022
...
4 60530 40885>60532>60524>60527>60530
5 10760 40885>60532>60524>60527>60530>10760
5 20365 40885>60532>60524>60527>60530>20365
...

please note, above pattern is very interesting, what I really want here is: I'd like to have the next level results are all from previous level, NOT just last record.

Thanks

|||

Did Kent's query answer you with the path? It seems to be the same (other than the fact that you are not returning the root node). A query like that was going to be my next suggeston.

Instead of MAXRECURSION, you can also limit the level value in the where clause to make it stop too:

;with cte(levels, parentid, categoryid, tree, title) as (
select 1,
parentid,
categoryid,
cast(str(categoryId, 11) as varchar(120)),
title
from @.categories
where parentid = 0
union all
select p.levels+1,
c.parentid,
c.categoryid,
cast(tree + '/' + str(c.categoryId, 11) as varchar(120)),
c.title
from cte p join @.categories c on p.categoryid = c.parentid
where p.levels < 2 --this will get level one and two only (because p.levels + 1)
)
select levels,
parentId,
categoryId,
title,
replace(tree, ' ', '') as tree
from cte
order by tree

levels parentId categoryId title tree

-- -- -- - --

1 0 1 aaa 1

2 1 100 bbb 1/100

2 1 101 ccc 1/101

2 1 102 ddd 1/102

|||

Thanks Louis , that works

Bill