Friday, June 26, 2009

Solution for "TSQL Challenge 8" -Recursive CTE.

HI.
this is my first successful affort to solve T-SQL challange.This challange was published on
http://beyondrelational.com/ .this website is authored by wel known MVP Jacob..
you can vision mpre challanges on this site. this is the solution of Challenge-8 .
Problem:
Retrive data as Hierarchy
Given Data:

DECLARE @Employees TABLE
(
EmpID INT,
EmpName VARCHAR(20),
ReportsTo INT
)
INSERT INTO @Employees
(
EmpID,
EmpName,
ReportsTo
)
SELECT 1,
'Jacob',
NULL
UNION ALL
SELECT 2,
'Rui',
NULL
UNION ALL
SELECT 3,
'Jacobson',
NULL
UNION ALL
SELECT 4,
'Jess',
1
UNION ALL
SELECT 5,
'Steve',
1
UNION ALL
SELECT 6,
'Bob',
1
UNION ALL
SELECT 7,
'Smith',
2
UNION ALL
SELECT 8,
'Bobbey',
2
UNION ALL
SELECT 9,
'Steffi',
3
UNION ALL
SELECT 10,
'Bracha',
3
UNION ALL
SELECT 11,
'John',
5
UNION ALL
SELECT 12,
'Michael',
6
UNION ALL
SELECT 13,
'Paul',
6
UNION ALL
SELECT 14,
'Lana',
7
UNION ALL
SELECT 15,
'Johnson',
7
UNION ALL
SELECT 16,
'Mic',
8
UNION ALL
SELECT 17,
'Stev',
8
UNION ALL
SELECT 18,
'Paulson',
9
UNION ALL
SELECT 19,
'Jessica',
10
DECLARE @manager VARCHAR(20) ;
SELECT @manager = 'smith' ;


Solution:


WITH Managers
as (
--initialization
SELECT EmpID,
EmpName,
CAST(EmpName + '' AS VARCHAR(200)) Hierarchy,
ReportsTo,
1 AS Position,
EmpName AS MainManager,
CAST(EmpID AS VARBINARY(500)) AS sort
FROM @Employees
UNION ALL
SELECT emp.EmpID,
emp.EmpName,
CAST(SPACE(4 * Position) + emp.EmpName AS VARCHAR(200)) Hierarchy,
emp.ReportsTo,
Position + 1,
MainManager,
CAST(sort + CAST(emp.EmpID AS BINARY(4)) AS VARBINARY(500)) AS sort
from @Employees emp
INNER JOIN Managers ON emp.ReportsTo = Managers.EmpID
)
SELECT Hierarchy
FROM Managers
WHERE MainManager = @manager
ORDER BY sort



Thanks.
Haresh Dhameliya