|
The following
RECURSIVE CTE T-SQL and SQL Server 2000 T-SQL recursive sproc scripts demonstrate the creation of an Organizational Chart for AdventureWorks Company based on the Employee Parent-Child (manager-employee) table.
-- AdventureWorks company Organizational Chart - Recursive Query
-- Recursive CTE - Common Table Expression - Tree processing - OrgChart
-- ROW_NUMBER() - automatic sequence generation
USE AdventureWorks;
WITH cteOrganizationalChart(ManagerID, EmployeeID, NodeLabel)
AS (SELECT ManagerID,
EmployeeID,
CAST('a' AS VARCHAR(16)) AS NodeLabel
FROM HumanResources.Employee
WHERE ManagerID IS NULL -- Anchor term - CEO - tree root
UNION ALL
-- Recursive term
SELECT e.ManagerID,
e.EmployeeID,
-- NodeLabel construction
cast(oc.NodeLabel + CHAR(96 + ROW_NUMBER()
OVER(ORDER BY LastName, FirstName)) AS VARCHAR(16))
FROM HumanResources.Employee e
INNER JOIN cteOrganizationalChart oc
ON e.ManagerID = oc.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID )
SELECT REPLICATE(' ',(LEN(NodeLabel)-1) * 5) + -- indentation
LastName+', '+FirstName AS Staff,
NodeLabel
FROM cteOrganizationalChart oc
JOIN HumanResources.Employee e
ON oc.EmployeeID = e.EmployeeID
JOIN Person.Contact c
ON c.ContactID = e.ContactID
ORDER BY NodeLabel;
| Staff |
NodeLabel |
| Sánchez, Ken |
a |
| Bradley, David |
aa |
| Benshoof, Wanida |
aaa |
| Brown, Kevin |
aab |
| Dempsey, Mary |
aac |
| Eminhizer, Terry |
aad |
| Gibson, Mary |
aae |
| Harnpadoungsataya, Sariya |
aaf |
| Williams, Jill |
aag |
| Wood, John |
aah |
| Duffy, Terri |
ab |
| Tamburello, Roberto |
aba |
| Cracium, Ovidiu |
abaa |
| D'Hers, Thierry |
abaaa |
| Galvin, Janice |
abaab |
| Erickson, Gail |
abab |
------------
------------
-- SQL Server 2000 and on T-SQL recursive query -- tree processing
------------
USE tempdb;
-- T-SQL create test data table with SELECT INTO
SELECT EmployeeID,
ManagerID,
FullName = FirstName + ' ' + LastName,
e.Title
INTO Employee
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN AdventureWorks.Person.Contact c
ON e.ContactID = c.ContactID
ORDER BY FullName
GO
-- Create table to store results
CREATE TABLE OrgChart (
ID INT IDENTITY ( 1 , 1 ),
Line VARCHAR(128));
GO
-- Recursive stored procedure is called from a WHILE loop iteratively
-- Tree level obtained from @@NESTLEVEL system variable
CREATE PROC uspTreeProcessing @Anchor INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmployeeID INT,
@FullName VARCHAR(48),
@Title VARCHAR(32),
@Level INT
SELECT @FullName = FullName, @Title = Title, @Level=@@NESTLEVEL
FROM Employee
WHERE EmployeeID = @Anchor
INSERT OrgChart (Line) -- Insert employee info indented
SELECT REPLICATE(' ',7 * @Level) + @FullName + ' - ' + @Title + ' ' +
CONVERT(VARCHAR, @Level)
SELECT @EmployeeID = min(EmployeeID)
FROM Employee
WHERE ManagerID = @Anchor
WHILE (@EmployeeID IS NOT NULL) -- iteration till no leaf reached
BEGIN
EXEC uspTreeProcessing @EmployeeID -- recursive sproc call
SELECT @EmployeeID = min(EmployeeID)
FROM Employee
WHERE ManagerID = @Anchor AND EmployeeID > @EmployeeID
END -- while
END -- sproc
GO
-- T-SQL execture stored procedure
EXEC uspTreeProcessing 109 -- CEO
-- Display organizational chart
SELECT Line FROM OrgChart ORDER BY ID
GO
/*
Line
Ken Sánchez - Chief Executive Officer 1
David Bradley - Marketing Manager 2
Kevin Brown - Marketing Assistant 3
Sariya Harnpadoungsataya - Marketing Specialist 3
Mary Gibson - Marketing Specialist 3
Jill Williams - Marketing Specialist 3
Terry Eminhizer - Marketing Specialist 3
.......
*/
DROP PROC uspTreeProcessing
DROP TABLE tempdb.dbo.Employee
DROP TABLE tempdb.dbo.OrgChart |