Posted by: R Manimaran | June 30, 2008

SQL Server Recursive Query

For our example create the following table..

iF (OBJECT_ID ('dbo.SampleOrg', 'U') IS NOT NULL)
  DROP TABLE dbo.SampleOrg
GO
CREATE TABLE dbo.SampleOrg
 (
	LevelID		INT NOT NULL PRIMARY KEY,
   Position		NVARCHAR(50) NOT NULL,
   ReportingLevelID	INT REFERENCES dbo.SampleOrg (LevelID)
 )
GO
-- Insert some sample data into the table based on the structure
-- shown above
INSERT INTO dbo.SampleOrg SELECT 1, 'Chief Executive Officer', NULL
INSERT INTO dbo.SampleOrg SELECT 2, 'Senior Director - Development', 1
INSERT INTO dbo.SampleOrg SELECT 3, 'Senior Director - Finance', 1
INSERT INTO dbo.SampleOrg SELECT 4, 'Senior Director - Human Resources', 1
INSERT INTO dbo.SampleOrg SELECT 5, 'Product Development Manager', 2
INSERT INTO dbo.SampleOrg SELECT 6, 'Project Lead', 5
INSERT INTO dbo.SampleOrg SELECT 7, 'QA Lead', 5
INSERT INTO dbo.SampleOrg SELECT 8, 'Documentation Lead', 5
INSERT INTO dbo.SampleOrg SELECT 9, 'Developers', 6
INSERT INTO dbo.SampleOrg SELECT 10, 'Testers', 7
INSERT INTO dbo.SampleOrg SELECT 11, 'Writers', 8
INSERT INTO dbo.SampleOrg SELECT 12, 'Accountants', 3
INSERT INTO dbo.SampleOrg SELECT 13, 'HR Professionals', 4

Sample 1: Show the levels that directly report to the Product Development Manager

WITH SampleOrgChart (Level, Position, ReportingLevel, OrgLevel, SortKey) AS
 (
  -- Create the anchor query. This establishes the starting
  -- point
  SELECT
     a.LevelID, a.Position, a.ReportingLevelID, 0,
     CAST (a.LevelID AS VARBINARY(900))
   FROM dbo.SampleOrg a
   WHERE a.Position = 'Product Development Manager'
  UNION ALL
  -- Create the recursive query. This query will be executed
  -- until it returns no more rows
  SELECT
    a.LevelID, a.Position, a.ReportingLevelID, b.OrgLevel+1,
    CAST (b.SortKey + CAST (a.LevelID AS BINARY(4)) AS VARBINARY(900))
   FROM dbo.SampleOrg a
     INNER JOIN SampleOrgChart b ON a.ReportingLevelID = b.Level
   WHERE b.OrgLevel < 1
 )
SELECT * FROM SampleOrgChart ORDER BY SortKey
GO

Let’s understand what this query does:

  1. First, we create the anchor member as the record which is for the Product Development Manager. As part of this query, we create two pseudo columns. One for indicating the level (called OrgLevel) and for sorting the records in the right fashion (called SortKey). The sort key for us is the primary key of the table converted to a binary column.
  2. After the anchor query, we now use this as the input and form the recursive query. Note that the recursive query increments the OrgLevel column and also builds the SortKey column.
  3. Since we want only the people who directly report to the product development manager, we specify the condition OrgLevel < 1. What happens if we omit this condition?

Sample 2: Show all the levels that report to the Product Development Manager

Here is the query that provides all the levels that reports to the product development manager.

WITH SampleOrgChart (Level, Position, ReportingLevel, OrgLevel, SortKey) AS
 (
  -- Create the anchor query. This establishes the starting
  -- point
  SELECT
     a.LevelID, a.Position, a.ReportingLevelID, 0,
     CAST(a.LevelID AS VARBINARY(900))
   FROM dbo.SampleOrg a
   WHERE a.Position 	= 'Product Development Manager'
  UNION ALL
  -- Create the recursive query. This query will be executed
  -- until it returns no more rows
  SELECT
     a.LevelID, a.Position, a.ReportingLevelID, b.OrgLevel+1,
     CAST(b.SortKey + CAST (a.LevelID AS BINARY(4)) AS VARBINARY(900))
   FROM dbo.SampleOrg a
     INNER JOIN SampleOrgChart b ON a.ReportingLevelID = b.Level
 )

Thanks to SqlServerCentral.com

SELECT * FROM SampleOrgChart ORDER BY SortKey
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: