Reverse Treeview

• Alan Stanley

  SSChasing Mays

  Points: 660

  More actions

  October 19, 2011 at 12:27 am

  #1396467

  Jeff

  I mentioned that there were too many recursions using a second cte, and ordering the tree was nigh on impossible.

  We have a solution (or perhaps a botch).

  We have created a temporary table, deleted all the duplicate rows and leaving us with the required tree rows. Made Treelevel null added a sortkey field

  We then do a recursive loop to build the tree again - currently a max of 400 records.

  while exists (select * from #temp where [treelevel] Is Null)

  begin

  update t set t.[treelevel] = t2.[treelevel] + 1, t.[sortkey] = t2.[sortkey] + cast(t.[uid] as varchar)

  from #temp as t inner join #temp as t2 on (t.[cat_parentId] = t2.[uid])

  where t2.[treelevel] > = 0 and t2.[sortkey] is not null and t.[treelevel] is null

  end

  select uid,cat_name,cat_meta_title,cat_parentId,treelevel,sortkey from #temp

  order by sortkey,treelevel

  We get the desire result in less than a second whereas the second cte method was taking 2 minutes plus.

• Jeff Moden

  SSC Guru

  Points: 1004448

  More actions

  December 4, 2011 at 3:34 pm

  #1416442

  Alan Stanley (10/19/2011)

  ---

  Jeff

  I mentioned that there were too many recursions using a second cte, and ordering the tree was nigh on impossible.

  Heh... who said anything about using CTEs, especially recursive CTEs? If you premark the rows as I suggested, simple "descendent" queries using the HierarchyID will run in milli-seconds.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)