Expanding a tree?

Expanding a tree?

I ran into a problem for which I cannot find a solution that satisfies me.  I
wonder if someone would have something clever on this.

I have a traditional organizational tree table: EmployeeID, ManagerID, self
referencing.

What I need is to get from it a table of the structure ManagerID, EmployeeID,
where there is an entry for every direct OR indirect replationship between each
manager and each employee.  So it A manages B and Y, B manages C and X, and C
manages D:

DirectManagerID, EmployeeID
A,B
A,Y
B,C
B,X
C,D

or diagramatically:

A
|
+--> B  Y
|
+--> C  X
|
+--> D

then I want to produce a table:

ManagerID, EmployeeID
A,B
A,C
A,D
A,X
A,Y
B,C
B,X
B,D
C,D

I cannot predict the depth of the command chain, but I assume it to be something
like 15-16 at most (above diagram shows 4 levels).

Any clever solutions?

Problem B:

A more complicated variant is that the EmployeeID may be NON-unique -- an
employee may have more than one manager if s/he works on more than one project.

DirectManagerID, EmployeeID
A,B
A,Y
B,C
B,X
C,D
M,C

A
|
+-->  B  Y           M
|              |
+--> X  C <----+ [M co-manages C only, M does NOT manage X]
|
+--> D

ManagerID, EmployeeID
A,B
A,C
A,D
A,X
A,Y
B,C
B,X
B,D
C,D
M,C
M,D

Any clever ideas on this one?

Thanks

I got tired of spam, so my email address is protected.  If you really need to
know what it is, please utilize http://www.rtg.se/~niclas/rot13 or
http://www.rtg.se/~niclas/reddwarf/rot13, either followed by a period and an
"htm".

Expanding a tree?

Give this a try...

USE NORTHWIND
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'pFindHierarchy' AND type =
'P')
DROP PROCEDURE pFindHierarchy
GO

AS
SET NOCOUNT ON

CREATE TABLE #TempTable (EmployeeID INT, ReportsTo INT, nLevel INT,
Hierarchy VARCHAR(255))

BEGIN

BEGIN
PRINT 'You are at top of hierarchy'
RETURN
END
ELSE
BEGIN

WHILE EXISTS (SELECT *
FROM #TempTable t INNER JOIN employees e
ON t.ReportsTo = e.EmployeeID
WHERE t.ReportsTo NOT IN (SELECT EmployeeID FROM
#TempTable))
BEGIN

INSERT INTO #TempTable

FROM #TempTable t INNER JOIN Employees e
ON t.ReportsTo = e.EmployeeID
WHERE t.ReportsTo NOT IN (SELECT EmployeeID FROM #TempTable)
END

UPDATE #TempTable

BEGIN
UPDATE t1
SET Hierarchy = t2.Hierarchy + '.' +  t1.Hierarchy
FROM #TempTable t1 INNER JOIN #TempTable t2 ON t2.EmployeeID =
t1.ReportsTo

END
END
END
ELSE

BEGIN

IF NOT EXISTS(SELECT *
FROM #TempTable t INNER JOIN employees e
ON t.EmployeeID = e.ReportsTo
WHERE e.EmployeeID NOT IN (SELECT EmployeeID FROM
#TempTable))
BEGIN
PRINT 'There are no children'
RETURN
END
WHILE EXISTS (SELECT *
FROM #TempTable t INNER JOIN employees e
ON t.EmployeeID = e.ReportsTo
WHERE e.EmployeeID NOT IN (SELECT EmployeeID FROM
#TempTable))
BEGIN

INSERT INTO #TempTable

CAST(e.EmployeeID as VARCHAR(3))
FROM #TempTable t INNER JOIN Employees e
ON t.EmployeeID = e.ReportsTo
WHERE e.EmployeeID NOT IN (SELECT EmployeeID FROM #TempTable)
END
END

--SELECT TOP 1 COUNT(*) as LevelCount , nlevel FROM #TempTable GROUP BY
nLevel ORDER BY 1 DESC

SELECT REPLICATE(' . ',nLevel) + e.LastName as Employee
FROM Employees e INNER JOIN #TempTable t ON e.EmployeeID = t.EmployeeID
ORDER BY Hierarchy
ELSE
SELECT * FROM #TempTable ORDER BY Hierarchy
GO

pFindHierarchy 2

--
Darren Brinksneader MCDBA, MCSE+I, CNE, CCA, MCT, CTT

Quote:> I ran into a problem for which I cannot find a solution that satisfies me.
I
> wonder if someone would have something clever on this.

> I have a traditional organizational tree table: EmployeeID, ManagerID,
self
> referencing.

> What I need is to get from it a table of the structure ManagerID,
EmployeeID,
> where there is an entry for every direct OR indirect replationship between
each
> manager and each employee.  So it A manages B and Y, B manages C and X,
and C
> manages D:

> DirectManagerID, EmployeeID
> A,B
> A,Y
> B,C
> B,X
> C,D

> or diagramatically:

> A
> |
> +--> B  Y
> |
> +--> C  X
> |
> +--> D

> then I want to produce a table:

> ManagerID, EmployeeID
> A,B
> A,C
> A,D
> A,X
> A,Y
> B,C
> B,X
> B,D
> C,D

> I cannot predict the depth of the command chain, but I assume it to be
something
> like 15-16 at most (above diagram shows 4 levels).

> Any clever solutions?

> Problem B:

> A more complicated variant is that the EmployeeID may be NON-unique -- an
> employee may have more than one manager if s/he works on more than one
project.

> DirectManagerID, EmployeeID
> A,B
> A,Y
> B,C
> B,X
> C,D
> M,C

> A
> |
> +-->  B  Y           M
> |              |
> +--> X  C <----+ [M co-manages C only, M does NOT manage X]
> |
> +--> D

> ManagerID, EmployeeID
> A,B
> A,C
> A,D
> A,X
> A,Y
> B,C
> B,X
> B,D
> C,D
> M,C
> M,D

> Any clever ideas on this one?

> Thanks

> I got tired of spam, so my email address is protected.  If you really need
to
> know what it is, please utilize http://www.rtg.se/~niclas/rot13 or
> http://www.rtg.se/~niclas/reddwarf/rot13, either followed by a period and
an
> "htm".

Expanding a tree?

Look up the Nested set model for heirarchies in the archives.  You can do what you want in one simple query.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Has anyone else found that trying to expand the Databases tree using SEM
2000 takes much longer than in 7.0?  I ran a trace of what's going on, and
found that the following query was being run once for each database on the
server, despite the fact that the query returns status information for all
the databases.  On my laptop this query takes around 2 seconds to run, which
for my 12 databases means a wait of about 30 seconds.  On our test server
with 126 databases, the query runs in 600 ms, resulting in over a minute's
wait just to list the databases.

Worse, some operations in SEM (in particular, restoring a database) cause
the whole process to be repeated!  So going into Entrprise Manager to
restore a database (something I do several times a day) might take three
minutes, before I can even start the restore.

select name, DATABASEPROPERTY(name, N'IsDetached'),     (case when
DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else
DATABASEPROPERTY(name, N'IsShutdown') end),     DATABASEPROPERTY(name,
N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'),