hi,i want to diplayfirst the parant and then all it's subsequent children at differentlevel, then second parant and all it's subsequent children , parentelements is null with levelid=1 i am using sql server 2000,and i don;twant to pass any root elemenet id, it should bring from start to end. input id name parentid levelid 1 a NULL 1 2 b 1 2 3 c 1 2 4 d 2 3 5 e 4 2 6 f NULL 1 7 g 6 2 8 h 7 3 i want the output like this id name parentid levelid 1 a NULL 1 2 b 1 2 3 d 2 3 4 c 1 2 5 e 4 2 6 f NULL 1 7 g 6 2 8 h 7 3
In SQL Server 2000 there is no simple way to create recursive queries that have several levels of data (hierarchical data). There are various links available on web such as http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm and other implementation from MS, http://support.microsoft.com/default.aspx?scid=kb;en-us;q248915 & http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sql03b8.asp If this is an ongoing application then better to think about upgrade to 2005 whereby you can take advantage of CTEs - http://msdn2.microsoft.com/en-us/library/ms186243.aspx link.