Blum, Jason (SAA) wrote: > > Columns: iEntityID, iSubEntityOf, sName > 1 1 Mary > 2 2 1 Fritz > 3 3 1 John > 4 4 2 Abu > 5 5 2 Ludwig > 6 6 3 Abigail > 7 7 3 Josef > 8 8 6 Mark > 9 9 6 Ben > 10 10 6 Habib > 11 11 9 Paul > 12 12 11 Mahatma > > I want to graphically represent the tree in this data - i.e. Mary is the > boss; Fritz and John report to Mary; Abu and Ludwig report to Fritz; > etc...
Google for "Adjacency List Model", there are plenty of stored procedures available to output this in a nested model. > I used to be able to do this in Oracle using: > > <CFQUERY NAME="GetEntities" DATASOURCE="MyDatasource"> > SELECT iEntityID, iSubEntityOf, sName > FROM Mytable > WHERE NOT iEntityID > START WITH iEntityID=1 > CONNECT BY PRIOR iEntityID=iSubEntityOf > </CFQUERY> > > But I can't use these functions in SQL Server... File an enhancement request and ask for "WITH ... RECURSIVE" support :) (That is how it is named in the SQL standard. DB2 already supports the WITH part, and I think I have seen RedHat patches for PostgreSQL that also use the standard syntax.) Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
