How to show expanding hierarchies by using SQL Server

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q248915


Databases frequently store hierarchical information. For more details abouthierarchical information, see the "Expanding Hierarchies" topic inMicrosoft SQL Server Books Online. The "Expanding Hierarchies" topic has adetailed Transact-SQL procedure that expands an encoded hierarchy to anyarbitrary depth.

NOTE: The SQL Books Online procedure declares @level, which is a reserved word in SQL Server 6.5 and SQL Server 7.0. Level is no longer a reserved word in SQL Server 2000 and in SQL Server 2005. The following code is a corrected version of this procedure in which @lvl is substituted for the reserved word so that it works on all versions. The procedure is enhanced to include the CREATE TABLE statement, CREATE INDEX samples, and INSERT statements that are required to demonstrate the example.

Following is the updated code sample:
CREATE TABLE hierarchy        (parent VARCHAR(20) NOT NULL,         child VARCHAR(20),CONSTRAINT UIX_parentchild         UNIQUE NONCLUSTERED (parent,child))CREATE CLUSTERED INDEX CIX_parent ON hierarchy(parent)GOINSERT hierarchy VALUES('World','Europe')INSERT hierarchy VALUES('World','North America')INSERT hierarchy VALUES('Europe','France')INSERT hierarchy VALUES('France','Paris')INSERT hierarchy VALUES('North America','United States')INSERT hierarchy VALUES('North America','Canada')INSERT hierarchy VALUES('United States','New York')INSERT hierarchy VALUES('United States','Washington')INSERT hierarchy VALUES('New York','New York City')INSERT hierarchy VALUES('Washington','Redmond')GOCREATE PROCEDURE expand (@current char(20)) as	--This is a non-recursive preorder traversal. SET NOCOUNT ON DECLARE @lvl int, @line char(20) CREATE TABLE #stack (item char(20), lvl int)	--Create a tempory stack. INSERT INTO #stack VALUES (@current, 1)	--Insert current node to the stack. SELECT @lvl = 1				 WHILE @lvl > 0					--From the top level going down.	BEGIN	    IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)	        BEGIN	            SELECT @current = item	--Find the first node that matches current node's name.	            FROM #stack	            WHERE lvl = @lvl	            SELECT @line = space(@lvl - 1) + @current	--@lvl - 1 s spaces before the node name.	            PRINT @line					--Print it.	            DELETE FROM #stack	            WHERE lvl = @lvl	                AND item = @current	--Remove the current node from the stack.	            INSERT #stack		--Insert the childnodes of the current node into the stack.	                SELECT child, @lvl + 1	                FROM hierarchy	                WHERE parent = @current	            IF @@ROWCOUNT > 0		--If the previous statement added one or more nodes, go down for its first child.                        SELECT @lvl = @lvl + 1	--If no nodes are added, check its brother nodes.		END    	    ELSE	      	SELECT @lvl = @lvl - 1		--Back to the level immediately above.       	END 						--WhileGOEXEC expand 'World'				

The result is World North America United States Washington Redmond New York New York City Canada Europe France Paris
back to the top
For additional information, see the "Expanding Hierarchies" topic inMicrosoft SQL Server Books Online.

back to the top

Article ID: 248915 - Last Review: 12/20/2005 22:48:23 - Revision: 4.4

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 64-bit Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbhowtomaster KB248915