In the past few tips, we've discussed the implementation of hierarchies in DB2 and Oracle. The navigation of hierarchies can also be accomplished through Transact-SQL. In this tip and the next, I will use an example taken from Microsoft's SQL Server version 7 SQL documentation. The procedure takes a list of items and creates a hierarchy of them, as shown below. For this list of items:

Transact-SDL can create this hierarchy:

World 
    North America 
        Canada 
        United States 
            Washington 
                Redmond 
            New York 
                New York City 
    Europe 
        France 
            Paris