THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

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;

 

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

 

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.

Published Saturday, October 03, 2009 11:49 PM by John Paul Cook
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tom Kreyche said:

Best recursive CTE example I've seen so far! Especially nice doing the concatenation of the p.Name as sort. Most other examples do the basic employee-manager relationship but ignore (don't understand) how to display the complete heirarchy. ...Thanks, Tom

February 4, 2014 6:43 PM
 

Mauro said:

Hi! excellent example,now suppose I want to calculate how many of each item I need to build X Bikes, how should I multiple the values from the deeper nodes ? thanks!

April 5, 2014 10:09 PM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement