Recursive MS SQL Query

Transact-SQL
1

MS SQL
I'm attempting to look up the name of the vp that everyone reports up to. I believe I need to use a CTE, however, I'm struggling to put the query together. Any assistance or guidance would be greatly appreciated.

Sample table
CREATE TABLE #Temp1
(
UserName varchar(30),
UserTitle varchar(30),
ManagerName varchar(30)
);

INSERT INTO #Temp1
VALUES
('dave','CEO',null),
('bill','vp','dave'),
('mike','vp','dave'),
('dave','vp','mike'),
('linda','cashier','mike'),
('sherry','manager','mike'),
('dawn','assistant manager','bill'),
('tom','electrician','sherry'),
('todd','security guard','dawn');

The sql query output would contain UserName in one column and the name of vp they report up to, or null if no vp above them, in the next column. For example

dave, null
bill, null
mike, null
dave, mike
linda, mike
sherry, mike
dawn, bill
tom, mike
todd, bill

2

I suspect that no one even tried to answer this because there is no uniqueness to the name column. The must be another column to key off of. Perhaps an Employee_ID column?

1 Like
3

Hello

To find the VP that everyone reports up to, use this query:
WITH Hierarchy AS (
SELECT UserName, ManagerName
FROM #Temp1
WHERE UserTitle = 'vp'
UNION ALL
SELECT t.UserName, h.ManagerName
FROM #Temp1 t
JOIN Hierarchy h ON t.ManagerName = h.UserName
)
SELECT
t.UserName,
h.ManagerName AS ReportsToVP
FROM #Temp1 t
LEFT JOIN Hierarchy h ON t.UserName = h.UserName;

This query will provide the UserName and the name of the VP they report to, or null if there is no VP above them.

Thank you
gregbowers

4

The produces the following error for the given test data because, like I said earlier, the UserName column does not contain unique values...

Msg 530, Level 16, State 1, Line 20
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.