Oh... interesting. Would be nice to do it in SQL. I will study this. Thanks Donnie!
> -----Original Message----- > From: Donnie Carvajal [mailto:[email protected]] > Sent: Wednesday, March 04, 2009 9:52 AM > To: cf-talk > Subject: Re: displaying a tree structure > > > > I have data in a table like this: > > > > ID ParentID Name > > 1 0 Top > > 2 1 Level1 > > 3 1 Level2 > > 4 2 Child1 Of Level2 > > 5 2 Child2 Of Level2 > > 6 3 Child1 Of Level2 > > > > So the tree would look like this: > > > > Top > > Level1 > > Child1 Of Level1 > > Child2 Of Level1 > > Level2 > > Child1 Of Level2 > > > > Hopefully the tabs in the email will format when I send this so it is > > easy to read. > > > > How do I loop over this data to display the tree dynamically? I have > > used Loops with queries to get each levels childs, but the way I wrote > > it I have to have a loop for each level. What if I have unlimited > > childs? My loops will only display as many childs as I have loops. > > > > Maybe there is a better way to structure the data in the first place. > > > > Thanks! > > If you are using SLQ Server 2005 or higher, use can use recursive queries > using CTEs. There are several examples out there. The one I use is like > the following. > > WITH TreeCTE(cte_code, cte_name, cte_parent, cte_level, cte_sortCol) > AS > ( > SELECT code, name, parent, 0, > CAST(name + ' - ' + CAST(code AS VARCHAR(10)) AS > VARBINARY(900)) > FROM TreeNodes > WHERE parent IS NULL > GROUP BY code, name, parent > > UNION ALL > > SELECT code, name, parent, cte_level + 1, > CAST(cte_sortCol + CAST(name + ' - ' + CAST(code AS > VARCHAR(10)) AS VARBINARY(900)) AS VARBINARY(900)) > FROM TreeNodes > INNER JOIN > TreeCTE > ON parent = cte_code > ) > > SELECT cte_code AS code, cte_name AS name, cte_level > FROM TreeCTE > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320098 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
