July 31, 2014 at 9:01 am
Hi Friends,
I got this code from web. I cant understand how this works, anybody help me to understand this ?
CREATE TABLE Employee
(Employee_id INT PRIMARY KEY,
EmployeeName VARCHAR(100),
Manager_id INT
)
INSERT INTO Employee VALUES (1,'A',NULL)
INSERT INTO Employee VALUES (2,'A_B',1)
INSERT INTO Employee VALUES (3,'A_C',1)
INSERT INTO Employee VALUES (4,'A_D',1)
INSERT INTO Employee VALUES (5,'B_B',2)
INSERT INTO Employee VALUES (6,'B_C',2)
INSERT INTO Employee VALUES (7,'C_B',3)
INSERT INTO Employee VALUES (8,'C_C',3)
INSERT INTO Employee VALUES (9,'BB_B',5)
INSERT INTO Employee VALUES (10,'BB_C',5)
INSERT INTO Employee VALUES (11,'BC_B',6)
INSERT INTO Employee VALUES (12,'BC_C',6)
INSERT INTO Employee VALUES (13,'BBB_B',9)
INSERT INTO Employee VALUES (14,'BBC_B',10)
;WITH DirectReports(Manager_ID, ManagerName,Employee_ID, EmployeeName ,EmployeeLevel)
AS (
--Select the root or parent records
SELECT
Manager_ID,
CAST('' AS VARCHAR(100))AS ManagerName,
Employee_ID,
EmployeeName,
0 AS EmployeeLevel
FROM Employee
WHERE Manager_ID IS NULL
UNION ALL
--Recursive part :Select the child
SELECT
e.Manager_ID,
m.EmployeeName AS ManagerName,
e.Employee_ID,
e.EmployeeName,
EmployeeLevel + 1
FROMEmployee e
INNER JOINDirectReports d
ON e.Manager_ID = d.Employee_ID
INNER JOIN employee m ON e.manager_ID = m.employee_id
)
SELECT * FROM DirectReports ;
Thanks In Advance
July 31, 2014 at 9:15 am
What part(s) don't you understand?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2014 at 9:37 am
Sean Lange (7/31/2014)
What part(s) don't you understand?
the join part.. how it iterates all the data
FROMEmployee e
INNER JOINDirectReports d
ON e.Manager_ID = d.Employee_ID
INNER JOIN employee m ON e.manager_ID = m.employee_id
July 31, 2014 at 9:44 am
This is a good place to start. http://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx
The code is a recursive cte. There are tons of articles that explain how they work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply