Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Generating a tree structure from data Date: Wed, 22 Jun 2011 22:18:04 +0200 Organization: Erland Sommarskog Lines: 40 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx04.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="6940"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/bDC/a+IMF+vnUq3zzJ2Kr" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:Q/uzVU2PokadgOK+u3e7Zwk/Lwk= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:476 Steve (steve.simpson@aircelle.com) writes: > parent is either blank, Y (meaning there are child links) and H > (meaning it's the top level of a structure) That's an unusual column for that type of structure. I don't see what it adds. Usually this relation is encoded in the ids. And what does blank mean? > I want to be able to create a procedure that recusively loops through > the table (intradtl) and outputs an indented structure of the file > > Level description > ..0 <> > ...1 <> > .....2 <> > ...1 <> > etc So ignoring Parent, and assuming that you are on SQL 2005 or later, you can do: WITH rekurs AS ( SELECT icode, iddesc, lvl = 0 FROM intradt1 WHERE idcode IS NULL UNION ALL SELECT i. icode, i.iddesc, r.lvl + 1 FROM intradt1 i JOIN rekurs r ON i.idcode = i.code ) SELECT replicate('.', lvl + 2) + ltrim(str(lvl)), idddesc FROM rekurs -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx