Hierarchical Query In SQL

it can be done in SQL SERVER 2005 and later using CTE (common table expression)

DECLARE @Table1 AS TABLE
(
LevelID INT NOT NULL PRIMARY KEY,
Position NVARCHAR(50) NOT NULL,
ReportingLevelID INT
)

INSERT INTO @Table1 SELECT 1, 'Chief Executive Officer', NULL
INSERT INTO @Table1 SELECT 2, 'Senior Director - Development', 1
INSERT INTO @Table1 SELECT 3, 'Senior Director - Finance', 1
INSERT INTO @Table1 SELECT 4, 'Senior Director - Human Resources', 1
INSERT INTO @Table1 SELECT 5, 'Product Development Manager', 2
INSERT INTO @Table1 SELECT 6, 'Project Lead', 5
INSERT INTO @Table1 SELECT 7, 'QA Lead', 5
INSERT INTO @Table1 SELECT 8, 'Documentation Lead', 5
INSERT INTO @Table1 SELECT 9, 'Developers', 6
INSERT INTO @Table1 SELECT 10, 'Testers', 7
INSERT INTO @Table1 SELECT 11, 'Writers', 8
INSERT INTO @Table1 SELECT 12, 'Accountants', 3
INSERT INTO @Table1 SELECT 13, 'HR Professionals', 4

SELECT * FROM @Table1;

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 @Table1 a
WHERE a.Position = 'Chief Executive Officer'
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 @Table1 a
INNER JOIN SampleOrgChart b ON a.ReportingLevelID = b.[Level]
--WHERE b.OrgLevel < 1
)


SELECT * FROM SampleOrgChart ORDER BY SortKey

credit goes to Srinivas Sampath

until then