Hierarchical Queries in SQL Server 2005

Retrieving hierarchy in SQL Server 2005

Minimum effort would be:

• Go to Google and search for SQL Server recursive CTE tutorial: https://www.google.com/search?q=sql+server+recursive+cte+tutorial
• Open and read 1st link: https://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx

• Write a query such as:

  Declare @EmployeeID int = 4
  
  ; with list(EmployeeID, TeamLeadID) as (
      Select EmployeeID, TeamLeadID From Employees Where EmployeeID = @EmployeeID
      Union All
      Select e.EmployeeID, e.TeamLeadID From Employees E
          Inner Join list l On l.TeamLeadID = E.EmployeeID
      Where E.TeamLeadID is not NULL
  )
  Select TeamLeadID From list
  

Recursive Query using CTE in SQL Server 2005

edit OK having actually read the requirements and thought a bit this is actually quite easy (I think!)

The point is that we want two things: the category hierarchy, and a count of products. The hierarchy is done by a recursive CTE, and counting is done outside that:

-- The CTE returns the cat hierarchy:
-- one row for each ancestor-descendant relationship
-- (including the self-relationship for each category)
WITH CategoryHierarchy AS (
    -- Anchor member: self relationship for each category
    SELECT CategoryID AS Ancestor, CategoryID AS Descendant
    FROM Categories
UNION ALL
    -- Recursive member: for each row, select the children
    SELECT ParentCategory.Ancestor, Children.CategoryID
    FROM 
        CategoryHierarchy AS ParentCategory
        INNER JOIN Categories_XREF AS Children
        ON ParentCategory.Descendant = Children.ParentID
)
SELECT CH.Ancestor, COUNT(ProductID) AS ProductsInTree
-- outer join to product-categories to include 
-- all categories, even those with no products directly associated
FROM CategoryHierarchy CH
LEFT JOIN Products_Categories_XREF PC
ON CH.Descendant = PC.CategoryID
GROUP BY CH.Ancestor

The results are:

Ancestor    ProductsInTree
----------- --------------
942         13
943         13
959         9
960         1
961         3

I am indebted to this article by the inestimable Itzik Ben-Gan for getting my thinking kick-started. His book 'Inside MS SQL Server 2005: T-SQL Querying' is highly recommended.

How do I create a recursive query in MSSQL 2005?

On SQL Server 2005 you can use CTEs (Common Table Expressions) :

with Hierachy(CustomerID, ParentID, Name, Level)
as
(
select CustomerID, ParentID, Name, 0 as Level
    from Customers c
    where c.CustomerID = 2 -- insert parameter here
    union all
    select c.CustomerID, c.ParentID, c.Name, ch.Level + 1
    from Customers c
    inner join Hierachy ch
    on c.ParentId = ch.CustomerID
)
select CustomerID, ParentID, Name
from Hierachy
where Level > 0

Ordering hierarchy from recursive query results in SQL 2005

Solved the problem using a variation of Mark's method, but I'm not retaining the node path in every node, so I can more easily move them around the tree. Instead I changed my 'OrderBy' column from an int to varchar(3) left-padded with zeros so I can concatenate them into a master 'OrderBy' for all the rows returned.

with tasks (TaskId, ParentTaskId, OrderBy, [Name], RowOrder) as
(
    select  parentTasks.TaskId,
            parentTasks.ParentTaskId,
            parentTasks.OrderBy,
            parentTasks.[Name],
            cast(parentTasks.OrderBy as varchar(30)) 'RowOrder'
    from    Task parentTasks
    where   ParentTaskId is null

    union all

    select  childTasks.TaskId,
            childTasks.ParentTaskId,
            childTasks.OrderBy,
            childTasks.[Name],
            cast(tasks.RowOrder + childTasks.OrderBy as varchar(30)) 'RowOrder'
    from    Task childTasks
    join    tasks
    on      childTasks.ParentTaskId = tasks.TaskId
)

select * from tasks order by RowOrder

This returns:

TaskId  ParentTaskId  OrderBy  Name                              RowOrder
---------------------------------------------------------------------------
1       NULL          001      Task One                          001
15      1             001      Task One / Task One               001001
2       NULL          002      Task Two                          002
7       2             001      Task Two / Task One               002001
14      7             001      Task Two / Task One / Task One    002001001
8       2             002      Task Two / Task Two               002002
9       8             001      Task Two / Task Two / Task One    002002001
10      8             002      Task Two / Task Two / Task Two    002002002
11      8             003      Task Two / Task Two / Task Three  002002003
3       NULL          003      Task Three                        003
4       NULL          004      Task Four                         004
13      4             001      Task Four / Task One              004001
5       NULL          005      Task Five                         005
6       NULL          006      Task Six                          006    
17      NULL          007      Task Seven                        007
18      NULL          008      Task Eight                        008
19      NULL          009      Task Nine                         009
21      19            001      Task Nine / Task One              009001
20      NULL          010      Task Ten                          010

It doesn't allow for an unlimited hierarchy (max 10 levels / max 1000 children per parent node - if I'd started the OrderBy at 0) but more than enough for my needs.

Problem in displaying hierarchical records (SQL Server 2005) [SET BASED]

There are more elgeant ways, but this quick modification will work:

;With bottomupParentChild AS 
( 
    SELECT EmpId AS parents, ReportsTo,CAST(EmpName AS VARCHAR(1000)) AS [Path],  0 AS [Level], EmpName 
    FROM @Employees 
    WHERE EmpId = @childNode 
    UNION ALL 
    SELECT i.EmpId AS parents, i.ReportsTo, CAST(gp.[Path] + '/' + i.EmpName AS VARCHAR(1000)) AS [Path],  
         gp.[Level]+1 AS [Level],i.EmpName 

    FROM @Employees i 
    JOIN bottomupParentChild gp ON i.EmpId = gp.ReportsTo 
) 
, cteRows (TotalRows)
 as (select count(*) - 1 from bottomupParentChild)

SELECT ABS(Level - TotalRows), REPLICATE('    ', ABS(Level - TotalRows)) + EmpName as [Hierarchy] 
FROM bottomupParentChild 
 cross join cteRows
ORDER BY  [Path] desc; 

I added a second cte that gets the count of rows from the first cte, and use it to "flip" the hierarchy.

---

Related Topics