March 4, 2013 at 10:07 pm
Like this? (All the same queries, but modified with Null. This leaves Groucho out altogether).
create table staff(employee int primary key, employee_name varchar(10),
supervisor int null references staff (employee))
insert staff
values
(1, 'groucho', Null),
(2, 'chico', 1),
(3, 'harpo', 2),
(4, 'zeppo', 2),
(5, 'moe', 1),
(6, 'larry', 5),
(7, 'curly', 5),
(8, 'shemp', 5),
(9, 'joe', 8),
(10, 'curly joe', 9);
select chartdepth=1, employee=o2.employee, supervisor=o1.employee
into #org_chart
from staff o1 inner join staff o2 on (o1.employee=o2.supervisor)
where o1.supervisor is not null
insert into #org_chart
select distinct o1.chartdepth+1, o2.employee, o1.supervisor
from #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)
where o1.chartdepth=(select max(chartdepth) from #org_chart)
and o1.supervisor is not null
select s.employee_name, supervises='supervises', e.employee_name
from #org_chart o join staff s on (o.supervisor=s.employee)
inner join staff e on (o.employee=e.employee)
where o.supervisor<>o.employee
--Quote me
March 6, 2013 at 7:11 am
Ah...my apologies. I lost track of this thread. On my way to work. I'll try to post an example tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2013 at 8:30 pm
Ok. I'm totally confused as to what Ken was trying to demonstrate. Let's take his first query from your last post.
select chartdepth=1, employee=o2.employee, supervisor=o1.employee
into #org_chart
from staff o1 inner join staff o2 on (o1.employee=o2.supervisor)
where o1.supervisor is not null
The result of that query is the same as the following and requires no join at all because all that it is ultimately doing is returning the contents of the original table with an extra column added.
SELECT ChartDepth = 1,
Employee,
Supervisor
FROM dbo.Staff
;
The second "paragraph" of code is clever but it produces a very nasty Cartesian Product (full Cross Join). I don't know if that would resolve itself out to a simple hash join when the table get's larger but, if it doesn't, this code would take a millenia to run on what some folks consider to be a "small" million node hierarchy that frequents the world of MLMs and some parts lists.
The third "paragraph" of code is, again, clever, but I'm not sure that you'd ever want to display something like that for a larger hierarchy and so I don't understand the overall reason for demonstrating this method.
Getting back to (literally) the root of what I was talking about (Groucho needs to have a NULL for supervisor), the original code isn't easily modified to allow for such a thing. Again, although it's clever code, I'm not sure why anyone would want to display the output of this code.
I guess the question at this point is, what would you actually like to do with this hierarchy example? Something like this? Although I don't particularly like the column names or the table name, I kept them for comparison.
CREATE TABLE dbo.Staff
(
Employee INT NOT NULL,
Employee_Name VARCHAR(10) NOT NULL,
Supervisor INT NULL
CONSTRAINT PK_Staff
PRIMARY KEY CLUSTERED (Employee ASC),
CONSTRAINT FK_Staff_ManagerMustBeAnEmployee
FOREIGN KEY (Supervisor)
REFERENCES dbo.Staff (Employee),
CONSTRAINT CK_Staff_ManagerCannotBeSelf
CHECK (Supervisor<>Employee)
)
;
INSERT INTO dbo.Staff
(Employee,Employee_Name,Supervisor)
SELECT 1, 'Groucho' , NULL UNION ALL --1 UNION ALL
SELECT 2, 'Chico' , 1 UNION ALL
SELECT 3, 'Harpo' , 2 UNION ALL
SELECT 4, 'Zeppo' , 2 UNION ALL
SELECT 5, 'Moe' , 1 UNION ALL
SELECT 6, 'Larry' , 5 UNION ALL
SELECT 7, 'Curly' , 5 UNION ALL
SELECT 8, 'Shemp' , 5 UNION ALL
SELECT 9, 'Joe' , 8 UNION ALL
SELECT 10, 'Curly Joe' , 9
;
WITH
cteDirectReports AS
( --=== This gets the top-level supervisors(s).
-- This would be the "primer" for a loop.
SELECT Employee, Supervisor, Employee_Name, EmployeeLevel = 1,
HierarchicalPath = CAST('\'+CAST(Employee AS VARCHAR(10)) AS VARCHAR(4000))
FROM dbo.Staff
WHERE Supervisor IS NULL
UNION ALL
--==== This gets a level at a time using recursion of the CTE.
-- This would be the "body" of a loop.
SELECT e.Employee, e.Supervisor, e.Employee_Name, EmployeeLevel = d.EmployeeLevel + 1,
HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.Employee AS VARCHAR(10)) AS VARCHAR(4000))
FROM dbo.Staff e
INNER JOIN cteDirectReports d ON e.Supervisor = d.Employee
) --=== This simply formats for display. I wouldn't do this in real life, either.
SELECT Employee,
Supervisor,
Employee_Name = SPACE((EmployeeLevel-1)*4) + Employee_Name,
EmployeeLevel,
HierarchicalPath
FROM cteDirectReports
ORDER BY HierarchicalPath
;
That produces the following indented name result...
Employee SupervisorEmployee_Name EmployeeLevel HierarchicalPath
1 NULL Groucho 1 \1
2 1 Chico 2 \1\2
3 2 Harpo 3 \1\2\3
4 2 Zeppo 3 \1\2\4
5 1 Moe 2 \1\5
6 5 Larry 3 \1\5\6
7 5 Curly 3 \1\5\7
8 5 Shemp 3 \1\5\8
9 8 Joe 4 \1\5\8\9
10 9 Curly Joe 5 \1\5\8\9\10
The "Hierarchical Path" contains the chain of command from the top level all the way down to the individual employee. If done in a Binary(4) format, you can easily use it to do some really remarkable things. It's kind of like the "new" HierarchyID datatype but better, IMHO.
For a much more detailed explanation of how that works, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/72503/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2013 at 11:51 am
Looking, thank you!
--Quote me
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply