locked
How can we control the recursion in CTE? RRS feed

  • Question

  • 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

     

    Tuesday, May 15, 2007 8:10 PM

Answers

  • 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

    Wednesday, May 16, 2007 2:07 AM

All replies

  • 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.

    Tuesday, May 15, 2007 9:09 PM
  • 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

     

     

    Tuesday, May 15, 2007 9:09 PM
  • 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
    */

    Tuesday, May 15, 2007 9:20 PM
  • 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

    Tuesday, May 15, 2007 10:50 PM
  • 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

    Wednesday, May 16, 2007 2:07 AM
  • Thanks Louis , that works

     

    Bill

    Wednesday, May 16, 2007 5:49 PM