Displaying Hierarchical Data - Indenting the Output

When working with hierarchical data, people commonly request the output be indented for each level of the hierarchy. There are two key pieces to solving this query puzzle – knowing the level in the hierarchy and coming up with a sorting key for ordering the results. The following query works with hierarchies implemented using the adjacency list pattern. Since the table being queried is the Production.BillOfMaterials table from either AdventureWorks or AdventureWorks2008, it is necessary to add a restriction to limit the results to components (parts) that have no end date (i.e., are still in use).

;WITH BOMcte(ComponentID, Name, PerAssemblyQty, BOMLevel, ProductAssemblyID, Sort)
AS
(
    SELECT b.ComponentID,
           CAST(p.Name as nvarchar(100)),
           b.PerAssemblyQty,
           b.BOMLevel,
           b.ProductAssemblyID,

           CAST('\' + p.Name as nvarchar(254))
    FROM Production.BillOfMaterials AS b
    INNER JOIN Production.Product p
    on b.ComponentID = p.ProductID
    WHERE b.EndDate IS NULL  -- only retrieve components still being used
    and b.ComponentID = 775  -- specify a component to explode
    UNION ALL
    SELECT b.ComponentID,
           CAST(REPLICATE ('|    ' , b.BOMLevel) + p.Name as nvarchar(100)),
           b.PerAssemblyQty,
           b.BOMLevel,
           b.ProductAssemblyID,
           CAST(cte.Sort + '\' + p.Name as nvarchar(254))
    FROM Production.BillOfMaterials as b
    INNER JOIN Production.Product p
    on b.ComponentID = p.ProductID
    INNER JOIN BOMcte AS cte
    ON b.ProductAssemblyID = cte.ComponentID
    WHERE b.EndDate IS NULL  -- only retrieve components still being used
)
SELECT Name, PerAssemblyQty
FROM BOMcte
ORDER BY Sort;

What’s not so obvious is the trick used to sort the output rows properly. Add the Sort column from the CTE definition to see how the query works:

;WITH BOMcte(ComponentID, Name, PerAssemblyQty, BOMLevel, ProductAssemblyID, Sort)
AS
(

    SELECT b.ComponentID,
           CAST(p.Name as nvarchar(100)),
           b.PerAssemblyQty,
           b.BOMLevel,
           b.ProductAssemblyID,

           CAST('\' + p.Name as nvarchar(254))
    FROM Production.BillOfMaterials AS b
    INNER JOIN Production.Product p
    on b.ComponentID = p.ProductID
    WHERE b.EndDate IS NULL  -- only retrieve components still being used
    and b.ComponentID = 775  -- specify a component to explode
    UNION ALL
    SELECT b.ComponentID,
           CAST(REPLICATE ('|    ' , b.BOMLevel) + p.Name as nvarchar(100)),
           b.PerAssemblyQty,
           b.BOMLevel,
           b.ProductAssemblyID,
           CAST(cte.Sort + '\' + p.Name as nvarchar(254))
    FROM Production.BillOfMaterials as b
    INNER JOIN Production.Product p
    on b.ComponentID = p.ProductID
    INNER JOIN BOMcte AS cte
    ON b.ProductAssemblyID = cte.ComponentID
    WHERE b.EndDate IS NULL  -- only retrieve components still being used
)
SELECT Name, PerAssemblyQty, Sort
FROM BOMcte
ORDER BY Sort;

Name                                    PerAssemblyQty Sort
--------------------------------------- -------------- ---------------------------------------------------------------------
Mountain-100 Black, 38                  1.00           \Mountain-100 Black, 38
|    Chain                              1.00           \Mountain-100 Black, 38\Chain
|    Front Brakes                       1.00           \Mountain-100 Black, 38\Front Brakes
|    Front Derailleur                   1.00           \Mountain-100 Black, 38\Front Derailleur
|    |    Front Derailleur Cage         1.00           \Mountain-100 Black, 38\Front Derailleur\Front Derailleur Cage
|    |    Front Derailleur Linkage      1.00           \Mountain-100 Black, 38\Front Derailleur\Front Derailleur Linkage
|    HL Bottom Bracket                  1.00           \Mountain-100 Black, 38\HL Bottom Bracket
|    |    BB Ball Bearing               10.00          \Mountain-100 Black, 38\HL Bottom Bracket\BB Ball Bearing
|    |    |    Bearing Ball             10.00          \Mountain-100 Black, 38\HL Bottom Bracket\BB Ball Bearing\Bearing Ball

For each component in the assembly, a string containing the complete path from the top of the hierarchy to the component is constructed. Assuming that individual component names are unique, sorting by this full path ensures that the output sorts correctly. Notice the CAST statement for the Name column. If you don’t use the CAST statement, you’ll see this error message:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "Sort" of recursive query "BOMcte".

The UNION ALL statement wants matching data types for the second column. That's why CAST is used to make the second column in the anchor query the same length as the second column in the recursive query. When choosing the length for the CAST statement, you must specify an appropriately sized maximum length for your Sort column. The minimum level in the Production.BillOfMaterials table is 0 and the maximum is 4 for a total of 5 levels. The Name column has a maximum length of 50 characters. So, the maximum possible length for the Sort column is 5 times 50 plus 4 for the maximum number of separator characters (the | character), which is 254.

The BOMLevel column is used to tell the REPLICATE function how far to indent the component. If you need to adapt this query to a table that doesn’t store the level, you’ll have to compute one as the following query does:

;WITH BOMcte(ComponentID, Name, PerAssemblyQty, ComputedLevel, ProductAssemblyID, Sort)
AS
(
    SELECT b.ComponentID,
           CAST(p.Name as nvarchar(100)),
           b.PerAssemblyQty, 
           0,
           b.ProductAssemblyID,

           CAST('\' + p.Name as nvarchar(254))
    FROM Production.BillOfMaterials AS b
    INNER JOIN Production.Product p
    on b.ComponentID = p.ProductID
    WHERE b.EndDate IS NULL  -- only retrieve components still being used
    and b.ComponentID = 775  -- specify a component to explode
    UNION ALL
    SELECT b.ComponentID,
           CAST(REPLICATE ('|    ' , ComputedLevel) + p.Name as nvarchar(100)),
           b.PerAssemblyQty, 
           ComputedLevel + 1,
           b.ProductAssemblyID,
           CAST(cte.Sort + '\' + p.Name as nvarchar(254))
    FROM Production.BillOfMaterials as b
    INNER JOIN Production.Product p
    on b.ComponentID = p.ProductID
    INNER JOIN BOMcte AS cte
    ON b.ProductAssemblyID = cte.ComponentID
    WHERE b.EndDate IS NULL  -- only retrieve components still being used
)
SELECT Name, PerAssemblyQty
FROM BOMcte
ORDER BY Sort;

Name                                 PerAssemblyQty
------------------------------------ --------------
Mountain-100 Black, 38               1.00
Chain                                1.00
Front Brakes                         1.00
Front Derailleur                     1.00
|    Front Derailleur Cage           1.00
|    Front Derailleur Linkage        1.00
HL Bottom Bracket                    1.00
|    BB Ball Bearing                 10.00
|    |    Bearing Ball               10.00
|    |    Cone-Shaped Race           2.00
|    |    Cup-Shaped Race            2.00
|    |    Lock Ring                  1.00
|    HL Shell                        1.00
HL Crankset                          1.00
|    Chainring                       3.00
|    Chainring Bolts                 3.00
|    Chainring Nut                   3.00
|    Freewheel                       1.00
|    HL Crankarm                     2.00
HL Headset                           1.00
|    Adjustable Race                 1.00
|    Crown Race                      1.00
|    Headset Ball Bearings           8.00
|    Keyed Washer                    1.00
|    Lock Nut 19                     1.00
|    Lower Head Race                 1.00
HL Mountain Frame - Black, 38        1.00
|    Chain Stays                     2.00
|    |    Metal Sheet 5              1.00
|    Decal 1                         2.00
|    Decal 2                         1.00
|    Down Tube                       1.00
|    |    Metal Sheet 3              1.00
|    Head Tube                       1.00
|    |    Metal Sheet 4              1.00
|    HL Fork                         1.00
|    |    Blade                      2.00
|    |    |    Metal Sheet 5         1.00
|    |    Fork Crown                 1.00
|    |    |    Metal Sheet 5         1.00
|    |    Fork End                   2.00
|    |    |    Metal Sheet 2         1.00
|    |    Steerer                    1.00
|    |    |    Metal Sheet 6         1.00
|    Paint - Black                   8.00
|    Seat Stays                      4.00
|    |    Metal Sheet 7              1.00
|    Seat Tube                       1.00
|    |    Metal Bar 2                1.00
|    Top Tube                        1.00
|    |    Metal Sheet 2              1.00
HL Mountain Front Wheel              1.00
|    HL Hub                          1.00
|    |    HL Shell                   1.00
|    |    HL Spindle/Axle            1.00
|    HL Mountain Rim                 1.00
|    HL Mountain Tire                1.00
|    HL Nipple                       36.00
|    Mountain Tire Tube              1.00
|    Reflector                       2.00
|    Spokes                          36.00
HL Mountain Handlebars               1.00
|    Handlebar Tube                  1.00
|    |    Metal Sheet 6              1.00
|    HL Grip Tape                    40.00
|    Mountain End Caps               2.00
|    |    Metal Sheet 2              1.00
|    Stem                            1.00
|    |    Metal Bar 1                1.00
HL Mountain Pedal                    1.00
HL Mountain Rear Wheel               1.00
|    HL Hub                          1.00
|    |    HL Shell                   1.00
|    |    HL Spindle/Axle            1.00
|    HL Mountain Rim                 1.00
|    HL Mountain Tire                1.00
|    HL Nipple                       36.00
|    Mountain Tire Tube              1.00
|    Reflector                       2.00
|    Spokes                          36.00
HL Mountain Seat Assembly            1.00
|    HL Mountain Seat/Saddle         1.00
|    Pinch Bolt                      4.00
|    Seat Lug                        1.00
|    Seat Post                       1.00
Rear Brakes                          1.00
Rear Derailleur                      1.00
|    Guide Pulley                    1.00
|    Rear Derailleur Cage            1.00
|    Tension Pulley                  1.00

You may want to add other columns to your output such as the level number and maybe the ComponentID.

The query can be easily modified to be a where used query, as shown here.