recursive hierarchy help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

recursive hierarchy help

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2012-07-19 : 20:34:13

Can someone help me get the desired output below?
I have the recursive CTE for the employee hierarchy working, but I want to order it so that you'll have a supervisor with his direct reports, another supervisor with his direct reports etc.. I spent all day trying to figure it out, but i'm totally lost.

--Sample data below:

--POPULATE EMPLOYEE SAMPLE TABLE

CREATE TABLE #TABLE1 (
NAME VARCHAR(MAX),
EMPLOYEEID INT,
SUPERVISORID INT

)

-- INSERT EMPLOYEES
INSERT INTO #TABLE1 VALUES('JEFF', 89, 90)
INSERT INTO #TABLE1 VALUES('BRIAN', 88, 89)
INSERT INTO #TABLE1 VALUES('JOE', 80, 88)
INSERT INTO #TABLE1 VALUES('DOUG', 81, 88)
INSERT INTO #TABLE1 VALUES('DAVE', 82, 88)
INSERT INTO #TABLE1 VALUES('VIC', 85, 89)
INSERT INTO #TABLE1 VALUES('MARK', 70, 89)
INSERT INTO #TABLE1 VALUES('TERRY', 71, 70)
INSERT INTO #TABLE1 VALUES('MIKE', 72, 70)
INSERT INTO #TABLE1 VALUES('ARNOLD', 73, 70)

--DECLARE TOP LEVEL ANCHOR
DECLARE @NAME1 AS VARCHAR(MAX)
SET @NAME1 = 'JOHN'

--RECURSIVE CTE
; WITH ORGCHART (NAME, EMPLOYEEID, SUPERVISORID, LEVEL) AS
(
SELECT NAME, EMPLOYEEID, SUPERVISORID, 1 AS LEVEL
FROM #TABLE1
WHERE NAME = @NAME1
UNION ALL
SELECT E.NAME, E.EMPLOYEEID, E.SUPERVISORID, LEVEL + 1
FROM #TABLE1 E JOIN ORGCHART M ON E.SUPERVISORID = M.EMPLOYEEID
)

SELECT * FROM ORGCHART

--DESIRED OUTPUT

NAME EMPLOYEEID SUPERVISORID LEVEL
--JOHN 89 90 1 --TOP LEVEL ANCHOR
--VIC 85 89 2-- HAS A DIRECT REPORT WHO HAS NOBODY REPORTING TO HIM
--BILL 88 89 2 -- SHOWS BILL AND HIS TEAM
--JOE 80 88 3
--DOUG 81 88 3
--DAVE 82 88 3
--MARK 70 89 2-- SHOWS MARK AND HIS TEAM
--TERRY 71 70 3
--MIKE 72 70 3
--ARNOLD 73 70 3

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-19 : 21:08:14

The data in your sample table and the desired output does not seem to match - for example, JOHN and BILL in the desired output, but not in the data. So I am guessing here; if this is not exactly it, please reply:

--RECURSIVE CTE
; WITH ORGCHART (NAME, EMPLOYEEID, SUPERVISORID, LEVEL,OrderingId) AS
(
SELECT NAME, EMPLOYEEID, SUPERVISORID, 1 AS LEVEL , 
	CAST(EMPLOYEEID AS VARCHAR(MAX))
FROM #TABLE1
WHERE NAME = @NAME1 
UNION ALL
SELECT E.NAME, E.EMPLOYEEID, E.SUPERVISORID, LEVEL + 1,
	m.OrderingId + '/' + CAST(e.EmployeeId AS VARCHAR(MAX))
FROM #TABLE1 E JOIN ORGCHART M ON E.SUPERVISORID = M.EMPLOYEEID
)

SELECT * FROM ORGCHART ORDER BY OrderingId

joe8079
Posting Yak Master

127 Posts

Posted - 2012-07-19 : 21:16:56

You are a genius! That works perfectly. My co worker and I did were going for the same concept, but made it WAY more complicated that it needed to be.

Thank you!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-19 : 21:35:38

You are very welcome, but I am not the genius, it is Itzik Ben-Gan (and others. This is a well-known problem). I have seen exactly this problem in his T-SQL book, although I am sure what he has in the book is probably more elegant and compact - I don't quite recall the details.

If you work a lot with hierarchies, since you are on SQL 2008, you might want to check the hierarchy id data type that is available in SQL 2008. http://technet.microsoft.com/en-us/library/bb677290.aspx It has some nice features.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources