Expand hierarchy
The database often stores hierarchical information. For example, the following data is a hierarchical representation of some parts of the world. This representation does not clearly show the structure implied in the data.
Parent child -------------------------------------------------------------------- -------------------- World Europe World North America Europe France France Paris North America United States North America Canada United States New York United States Washington New York New York City Washington Redmond
The following example is more easily explained:
World North America Canada United States Washington Redmond New York New York City Europe France Paris
The following Transact-SQL procedure expands an encoded hierarchy to any depth. Although Transact-SQL supports recursive, use a temporary table as a stack to track all items in processing (already started but have not yet ended), will be more effective. Once a project is processed, it will be removed from the stack. These items will be added to the stack when new projects are found.
Create Procedure Expand (@Current Char (20)) AS Set NoCount on Declare @LEVEL INT, @line Char (20) Create Table #stack (Item Char (20), Level Int) Insert Into #stack Values (@current, 1 ) Select @LEVEL = 1 While @LEVEL> 0 Begin if EXISTS (Select * from #stack where level = @Lom # SEGIN SELECT @current = item from #stack where level = @LEVEL SELECT @Line = Space (@Level - 1 ) @current PRINT @line DELETE FROM #stack WHERE level = @level AND item = @current INSERT #stack SELECT child, @level 1 FROM hierarchy WHERE parent = @current IF @@ ROWCOUNT> 0 SELECT @level = @level 1 end else select @LEVEL = @LEVEL - 1 End - while
The input parameter (@current) represents the start position in the hierarchy. It also tracks the current project in the main loop.
The two local variables used are @LesLEL (for tracking the current level in the hierarchy) and @line (which is a work area for constructing).
Set NoCount ON statement avoids the output of the RowCount message generated in each Select in the output.
Use the item identifier in the hierarchy to create and organize the temporary table #stack, and @LEVEL is set to match it. #stack's Level column allows the same project to appear in multiple levels of the database. Although this condition is not suitable for geographic data in this example, it can be used in other examples. In the example below, when @LEVEL is greater than 0, the process performs the following steps:
If there is any item in the current level (@ledel), the process will select one of them, and call it @current. Indenge the project @Level space, then print the project. Remove the item from the stack to avoid processes it, then add all the subcompices to the next level of the stack (@LEVEL 1). This is the only place to use the hierarchy (#stack). If you use a traditional programming language, you must find each sub-item and add it to the stack one by one. Use Transact-SQL, only one statement can be found and added all sub-items to avoid using a nesting loop. If there is a sub-item (if @@ rowcount> 0), the level is dropped at level (@LEVEL = @Level 1); otherwise, continue at the current level. Finally, if you don't want to proceed in the current level stack, return to the previous level, see if the item is to be processed (@LEVEL = @LEVEL - 1). When there is no last level, it is complete.
