I pulled this example from Books Online. I cannot figure out how to make
the CTE return the data in a different order.
I don't want the data ordered by the Level. I want the child data to appear
directly under the parent data. Is that possible?
USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0
GO
-- Here is the output from the CTE:
ManagerID EmployeeID Title
Level
-- -- ---- -
--
NULL 109 Chief Executive Officer 0
109 12 Vice President of Engineering 1
12 3 Engineering Manager 2
3 4 Senior Tool Designer 3
3 9 Design Engineer 3
3 11 Design Engineer 3
3 158 Research and Development Manager 3
3 263 Senior Tool Designer 3
3 267 Senior Design Engineer 3
3 270 Design Engineer 3
263 5 Tool Designer 4
263 265 Tool Designer 4
158 79 Research and Development Engineer 4
158 114 Research and Development Engineer 4
158 217 Research and Development Manager 4
-- Here is my desired output:
ManagerID EmployeeID Title
Level
-- -- ---- -
--
NULL 109 Chief Executive Officer 0
109 12 Vice President of Engineering 1
12 3 Engineering Manager 2
3 9 Design Engineer 3
3 11 Design Engineer 3
3 270 Design Engineer 3
3 158 Research and Development Manager 3
158 79 Research and Development Engineer 4
158 114 Research and Development Engineer 4
158 217 Research and Development Manager 4
3 267 Senior Design Engineer 3
3 263 Senior Tool Designer 3
263 5 Tool Designer 4
263 265 Tool Designer 4
3 4 Senior Tool Designer 3
Keith KratochvilKeith,
Here is an idea:
USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level,
PathToLevel)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 0 AS Level,
CAST(N'.' AS nvarchar(50)) AS PathToLevel
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, Level + 1,
CAST(PathToLevel+CAST(e.EmployeeId AS nvarchar(5))+N'.' AS nvarchar(50))
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level, PathToLevel
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0
ORDER BY PathToLevel
GO
Dejan Sarka
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OufHP8liGHA.4284@.TK2MSFTNGP05.phx.gbl...
>I pulled this example from Books Online. I cannot figure out how to make
>the CTE return the data in a different order.
> I don't want the data ordered by the Level. I want the child data to
> appear directly under the parent data. Is that possible?
>
> USE AdventureWorks;
> GO
> WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
> AS
> (
> -- Anchor member definition
> SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 0 AS Level
> FROM HumanResources.Employee AS e
> INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
> ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
> WHERE ManagerID IS NULL
> UNION ALL
> -- Recursive member definition
> SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, Level + 1
> FROM HumanResources.Employee AS e
> INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
> ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
> INNER JOIN DirectReports AS d
> ON e.ManagerID = d.EmployeeID
> )
> -- Statement that executes the CTE
> SELECT ManagerID, EmployeeID, Title, Level
> FROM DirectReports
> INNER JOIN HumanResources.Department AS dp
> ON DirectReports.DeptID = dp.DepartmentID
> WHERE dp.GroupName = N'Research and Development' OR Level = 0
> GO
>
> -- Here is the output from the CTE:
> ManagerID EmployeeID Title Level
> -- -- ----
> --
> NULL 109 Chief Executive Officer
> 0
> 109 12 Vice President of Engineering
> 1
> 12 3 Engineering Manager
> 2
> 3 4 Senior Tool Designer
> 3
> 3 9 Design Engineer
> 3
> 3 11 Design Engineer
> 3
> 3 158 Research and Development Manager
> 3
> 3 263 Senior Tool Designer
> 3
> 3 267 Senior Design Engineer
> 3
> 3 270 Design Engineer
> 3
> 263 5 Tool Designer
> 4
> 263 265 Tool Designer
> 4
> 158 79 Research and Development Engineer
> 4
> 158 114 Research and Development Engineer
> 4
> 158 217 Research and Development Manager
> 4
>
> -- Here is my desired output:
> ManagerID EmployeeID Title Level
> -- -- ----
> --
> NULL 109 Chief Executive Officer
> 0
> 109 12 Vice President of Engineering
> 1
> 12 3 Engineering Manager
> 2
> 3 9 Design Engineer
> 3
> 3 11 Design Engineer
> 3
> 3 270 Design Engineer
> 3
> 3 158 Research and Development Manager
> 3
> 158 79 Research and Development Engineer
> 4
> 158 114 Research and Development Engineer
> 4
> 158 217 Research and Development Manager
> 4
> 3 267 Senior Design Engineer
> 3
> 3 263 Senior Tool Designer
> 3
> 263 5 Tool Designer
> 4
> 263 265 Tool Designer
> 4
> 3 4 Senior Tool Designer
> 3
>
> --
> Keith Kratochvil
>
>|||Thanks for the solution, Dejan. I did some more poking around within Books
Online and found this:
http://msdn2.microsoft.com/en-us/library/ms175972.aspx
F. Using a recursive common table expression to display a hierarchical list
Now I have a couple of methods that I can use.
Keith Kratochvil
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eZuw%23OmiGHA.4304@.TK2MSFTNGP03.phx.gbl...
> Keith,
> Here is an idea:
> USE AdventureWorks;
> GO
> WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level,
> PathToLevel)
> AS
> (
> -- Anchor member definition
> SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 0 AS Level,
> CAST(N'.' AS nvarchar(50)) AS PathToLevel
> FROM HumanResources.Employee AS e
> INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
> ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
> WHERE ManagerID IS NULL
> UNION ALL
> -- Recursive member definition
> SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, Level + 1,
> CAST(PathToLevel+CAST(e.EmployeeId AS nvarchar(5))+N'.' AS nvarchar(50))
> FROM HumanResources.Employee AS e
> INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
> ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
> INNER JOIN DirectReports AS d
> ON e.ManagerID = d.EmployeeID
> )
> -- Statement that executes the CTE
> SELECT ManagerID, EmployeeID, Title, Level, PathToLevel
> FROM DirectReports
> INNER JOIN HumanResources.Department AS dp
> ON DirectReports.DeptID = dp.DepartmentID
> WHERE dp.GroupName = N'Research and Development' OR Level = 0
> ORDER BY PathToLevel
> GO
> --
> Dejan Sarka
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:OufHP8liGHA.4284@.TK2MSFTNGP05.phx.gbl...
>
