THE SQL Server Blog Spot on the Web

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

Paul Nielsen

www.SQLServerBible.com

Recursive CTEs in AdventureWorks 2008

The advent of HierarchyID removed the adjaceny pairs pattern (MangerID) from the employee table in AdventureWorks 2008. The recursive CTE example in BOL still uses the old AdventureWorks structure, so the code won't run in AdventureWorks 2008. I know that recursive CTE's are yesterday's news, but they're still useful as far as I'm concerned. So, if you want to learn about them and play with them in AdventureWorks 2008, here's some code to make your life easier...

(note: The new AdventureWorks 2008 does not include the adjacency pairs ManagerID column, the contacts table is changed to Person table, and the ContactID is changed to BusinessEntityID.)

-- Recreate Adjacency Pairs Pattern

ALTER TABLE HumanResources.Employee

 Add ManagerID INT;

 

go

 

-- Populate the new ManagerID Column using join condition to match

 

UPDATE E

  SET ManagerID = M.BusinessEntityID

  FROM HumanResources.Employee E

    JOIN HumanResources.Employee M

      ON M.OrganizationNode = E.OrganizationNode.GetAncestor(1);

 

 

 -- This query demos the recursive CTE

 

WITH OrgPath (BusinessEntityID, ManagerID, lv) 

AS (

   -- Anchor

      SELECT BusinessEntityID, ManagerID, 1

        FROM HumanResources.Employee

        WHERE ManagerID IS NULL -- should only be EmployeeID 1

        -- WHERE EmployeeID = 1 -- the CEO

 

    -- Recursive Call

    UNION ALL

      SELECT E.BusinessEntityID, E.ManagerID, lv + 1

        FROM HumanResources.Employee E

          JOIN OrgPath

            ON E.ManagerID = OrgPath.BusinessEntityID

    )

SELECT Emp.BusinessEntityID, Emp.JobTitle,

    C.FirstName + ' ' + C.LastName AS [Name],

    M.FirstName + ' ' + M.LastName AS [Manager], Lv

  FROM HumanResources.Employee Emp

    JOIN OrgPath

      ON Emp.BusinessEntityID = OrgPath.BusinessEntityID

    JOIN Person.Person AS C

      ON C.BusinessEntityID = Emp.BusinessEntityID

    Left Join Person.Person AS M

      ON Emp.ManagerID = M.BusinessEntityID

  ORDER BY Lv

 

Published Friday, October 03, 2008 5:09 PM by Paul Nielsen

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

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog

Syndication

News

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