ORACLE Enable conditionally equivalent in SQL Server

Question

Is there an equivalent clause for CONNECT BY Oracle in SQL Server. Requirement to create a category tree using the parentId field.

Answer 1

Oracle syntax syntax is CONNECT BYequivalent to SQL Server 2005+ equivalent to SQL query syntax to use recursive CTE. SQL Server 2008 added by HierarchyID . Here is an example of a recursive CTE:

WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS (
   SELECT EmployeeID,
          LastName,
          FirstName,
          ReportsTo,
          1 as HierarchyLevel
     FROM Employees
    WHERE ReportsTo IS NULL
   UNION ALL
   -- Recursive step
   SELECT e.EmployeeID,
          e.LastName,
          e.FirstName,
          e.ReportsTo,
          eh.HierarchyLevel + 1 AS HierarchyLevel
     FROM Employees e
     JOIN EmployeeHierarchy eh ON e.ReportsTo = eh.EmployeeID)
  SELECT *
    FROM EmployeeHierarchy
ORDER BY HierarchyLevel, LastName, FirstName 

Googling " CTE" / " CTE" . 4GuysFromRolla.com.

CTE ANSI - , Oracle 11g.

Answer 2

HierarchyID MS SQL Server 2008, .