SQL tree hierarchy order alphabetically but display as a tree

Question

This is an extension to an existing question for creating the sortorder parent child hierarchies.

How do I sort my table alphabetically, but with all child nodes in the right position? So after they have been assigned the right sort order, the parent nodes are not in alphabetical order, and each child level isn't either.

NOTE: the actual data that I'm using does not have the numbers at the bottom, they are just to illustrate the level of the hierarchy (e.g. piperoni is under pizza)

pizza   0.1
piperoni    0.1.4
cheese  0.1.5
extra cheese    0.1.5.7
vegetariana 0.1.6
burger  0.2
coffee  0.3

How do I change it to this?

burger  0.1
coffee  0.2
pizza   0.3
cheese  0.3.1
extra cheese    0.3.1.1
piperoni    0.3.2
vegetariana 0.3.3

See This

Poor data model... Remove dots, convert to int, order by (works as long as single digit numbers...) — jarlh, Apr 24 '15 at 11:42
ignore the numbers, they are only for illustration of the hierarchy. the data does NOT have numbers...I thought that was clear. — user3710760, Apr 24 '15 at 14:06
Added `sql-server` tag based on the syntax of the accepted answer. — , Apr 28 '15 at 22:13

RoughPlace · Accepted Answer · 2015-04-28T22:03:31.940

http://sqlblog.com/blogs/john_paul_cook/archive/2009/10/03/displaying-hierarchical-data-indenting-the-output.aspx

John Paul Cook wrote a useful query for this that i actually used earlier this week

in short it uses a recursive CTE and uses string concatenation to set the ordering

;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;

This is how i implemented it

WITH    Counts
      AS (
           SELECT   parent_ID ,
                    COUNT(id) AS cnt
           FROM     Contact_Heirarchy
           WHERE    Contact_Heirarchy.Discontinue_Date IS NULL
           GROUP BY Parent_Id
         ),
    Employee ( ID, [ParentId], Contact_ID, Name, ImmediateChildren )
      AS (
           SELECT   Company_Heirarchy_ID ,
                    Contact_Heirarchy.Parent_ID ,
                    c.contact_ID ,
                    c.Reporting_Name AS name ,
                    ISNULL(cn.cnt, 0) AS ImmediateChildren
           FROM     Contact_Heirarchy
                    LEFT OUTER JOIN Contact c ON Contact_Heirarchy.contact_ID = c.Contact_ID
                    LEFT OUTER JOIN Counts cn ON Contact_Heirarchy.Company_Heirarchy_ID = cn.parent_id
           WHERE    Contact_Heirarchy.Discontinue_Date IS NULL
         ),
    Tree
      AS (
           SELECT   [Id] ,
                    [ParentId] ,
                    Contact_ID ,
                    0 AS [TreeLevel] ,
                    CAST('\' + ( cast( 999 - ImmediateChildren as varchar(3)) ) + Name AS VARCHAR(1000)) AS Sort ,
                    CAST(REPLICATE('|    ', 0) + Name AS NVARCHAR(100)) Hierarchy ,
                    ParentNode.ImmediateChildren
           FROM     Employee AS ParentNode 
           WHERE    ( Contact_ID in(@contactID))
           UNION ALL
           SELECT   ChildNode.[Id] ,
                    ChildNode.[ParentId] ,
                    ChildNode.Contact_ID ,
                    LIT.[TreeLevel] + 1 AS [TreeLevel] ,
                    CAST(LIT.sort + '\' + (cast( 999 - ChildNode.ImmediateChildren as varchar(3)) ) + Name AS VARCHAR(1000)) AS Sort ,
                    CAST(REPLICATE('|    ', LIT.TreeLevel + 1) + Name AS NVARCHAR(100)),
                    ChildNode.ImmediateChildren
           FROM     Employee AS ChildNode 
                    INNER JOIN [Tree] LIT ON ( ChildNode.[ParentId] = LIT.[Id] )
           WHERE    ( ChildNode.[ParentId] IS NOT NULL )
         )
SELECT  Tree.* ,
        c.Reporting_Name AS Contact
FROM    Tree
        INNER JOIN Contact c ON Tree.Contact_ID = c.Contact_ID
ORDER BY Tree.Sort

SQL tree hierarchy order alphabetically but display as a tree

1 Answers1