CODEWITHSUNDEEP
sqlsql-serveroracle-databaset-sqlhierarchical-data
zapping
zapping

Reputation: 4116

ORACLE Connect by clause equivalent in SQL Server

Is there a equivalent clause to CONNECT BY of Oracle in SQL Server. The requirement to build a category tree using a parentId field.

Upvotes: 5

Views: 13434

Answers (2)

Ivan Krechetov
Ivan Krechetov

Reputation: 19220

There's HierarchyID data type in MS SQL Server 2008, which can make your life easier.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332661

The SQL Server 2005+ equivalent of Oracle's CONNECT BY hierarchical query syntax is to use a recursive CTE. SQL Server 2008 added HierarchyID. Here's 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 "hierarchical CTE" and/or "recursive CTE" will turn up numerous results. I took the example query from the 4GuysFromRolla.com.

Recursive CTEs are now ANSI standard - the syntax wasn't supported until Oracle 11g as I understand.

Upvotes: 9

Related Questions