DataTamer: SQL Server Services

Articles

Snippets

Date and Time

Documents

Useful Sites

SQL Server

Management Trees

Published: 2009/03/01

The following code prepares a sample table of staff then extracts a management tree starting from the senior employee.

-- sample table
CREATE TABLE [#staff]
	(
	[staffID] INT PRIMARY KEY
	,[managerID] INT
	,[staffName] VARCHAR(50)
	)

-- sample data
INSERT [#staff] SELECT 1,NULL,'CEO'
INSERT [#staff] SELECT 2,1,'VP Marketing'
INSERT [#staff] SELECT 3,1,'VP Operations'
INSERT [#staff] SELECT 4,1,'VP Sales'
INSERT [#staff] SELECT 5,2,'Tactical Marketing Director'
INSERT [#staff] SELECT 6,2,'Strategic Marketing Director'
INSERT [#staff] SELECT 7,3,'Operations Director'
INSERT [#staff] SELECT 8,4,'Tactical Sales Director'
INSERT [#staff] SELECT 9,4,'Strategic Sales Director'

; -- this is necessary to isolate the CTE
WITH [tree] ([staffID],[managerID],[staffName],[depth],[sortKey]) AS
	(
	SELECT -- this selects your top-level staff
		S.[staffID]
		,S.[managerID]
		,S.[staffName]
		,0 AS [depth]
		,cast(staffID AS VARBINARY(800)) AS [sortKey]
    FROM
		[#staff] AS S
	WHERE
		S.[staffID] = 1 -- ID of senior / starting staff
	UNION ALL
	SELECT -- this is the recursive portion - it selects the reporting staff
		S.[staffID]
		,S.[managerID]
		,S.[staffName]
		,T.[depth] + 1 AS [depth]
		,cast(T.[sortKey] + cast(S.[staffID] AS VARBINARY) AS VARBINARY(800)) AS [sortKey]
    FROM
		#staff AS S
		INNER JOIN [tree] AS T
			ON S.[managerID] = T.[staffID]
	)
SELECT -- the actual select statement that uses the CTE
	T.[staffID]
	,T.[managerID]
	,T.[staffName]
	,T.[depth]
	,T.[sortKey]
	,replicate(char(9),T.[depth]) + T.[staffName] AS [displayColumn]
FROM
	[tree] AS T
ORDER BY
	T.[sortKey]
OPTION (MAXRECURSION 10) -- setting this option helps prevent CTEs running in endless loops
;

-- clean-up
DROP TABLE [#staff]

Your result set should be a clean indented management tree such as the one below:

CEO
 VP Marketing
  Tactical Marketing Director
  Strategic Marketing Director
 VP Operations
  Operations Director
 VP Sales
  Tactical Sales Director
  Strategic Sales Director