EDIT: The script has been repaired and paired down.
Basically this "solution" assigns a derived value to each entity...which I called codex for lack of better term. Each Child inherits their Parents Codex node signature. In the code below I show how to add a new position in the tree and how to move an entire branch...not sure what else you would want to do, but if you let me know, I'll take a crack at. It also shows how to "mine" different element of meta data about the tree.
This is a link that launched this discussion
Ever wanted to do set based Hierarchical SQL? We came up with a twist, but you need to create a derived column AND it needs to be maintained, but this is something we implemented in a production system and seems to work very nicely. That was in DB2 and requires more effort, but the SQL Server version allows you to use more complex contructs than DB2. DB2 requires more "leg work" to do the same thing. So it is a "dumbed down" version of SQL. I'm sure the multiple host variable assignments can be consolidated, but I need this as a spec for now.
I offer this...now if someone has thought of this before, please let me know. In any case I built this without any refernce to anything else.
SET NOCOUNT ON
-- Create a table to hold a Position Tree
CREATE TABLE myPositions99 (Manager varchar(50), Employee varchar(50), codex varchar(800))
-- Create Work Tables for all the potential levels
CREATE TABLE Level1 (Level1Code int IDENTITY(1,1), Manager varchar(50), Employee varchar(50), codex varchar(800))
CREATE TABLE Level2 (Level2Code int IDENTITY(1,1), Manager varchar(50), Employee varchar(50), codex varchar(800))
CREATE TABLE Level3 (Level3Code int IDENTITY(1,1), Manager varchar(50), Employee varchar(50), codex varchar(800))
CREATE TABLE Level4 (Level4Code int IDENTITY(1,1), Manager varchar(50), Employee varchar(50), codex varchar(800))
CREATE TABLE Level5 (Level5Code int IDENTITY(1,1), Manager varchar(50), Employee varchar(50), codex varchar(800))
GO
-- Create a view of all the work tables
CREATE VIEW myView99 AS
SELECT * FROM Level1 UNION ALL
SELECT * FROM Level2 UNION ALL
SELECT * FROM Level3 UNION ALL
SELECT * FROM Level4 UNION ALL
SELECT * FROM Level5
GO
INSERT INTO myPositions99(Manager, Employee)
SELECT null , 'Gerard' UNION ALL
SELECT 'Gerard' , 'Mike' UNION ALL
SELECT 'Gerard' , 'Pat' UNION ALL
SELECT 'Gerard' , 'Dan' UNION ALL
SELECT 'Mike' , 'Nick' UNION ALL
SELECT 'Mike' , 'Erinn' UNION ALL
SELECT 'Mike' , 'Jeanne' UNION ALL
SELECT 'Pat' , 'Elene' UNION ALL
SELECT 'Pat' , 'Claudette' UNION ALL
SELECT 'Nick' , 'Algene' UNION ALL
SELECT 'Nick' , 'Brett' UNION ALL
SELECT 'Nick' , 'Lana' UNION ALL
SELECT 'Claudette' , 'Susan' UNION ALL
SELECT 'Claudette' , 'Tom' UNION ALL
SELECT 'Dan' , 'Bob' UNION ALL
SELECT 'Bob' , 'Ellen' UNION ALL
SELECT 'Erinn' , 'Nar' UNION ALL
SELECT 'Erinn' , 'Gary' UNION ALL
SELECT 'Erinn' , 'Pete'
GO
SELECT * FROM myPositions99
-- Populate all of the work tables with the employees at their particular levels
-- and assign the codex values
INSERT INTO Level1 (Manager, Employee)
SELECT l.Manager, l.Employee
FROM myPositions99 l
WHERE l.Manager IS NULL
UPDATE Level1 SET Codex = RIGHT(REPLICATE('0',5) + CONVERT(varchar(5),Level1Code),5) FROM Level1
INSERT INTO Level2 (Manager, Employee, Codex)
SELECT r.Manager, r.Employee, l.Codex
FROM Level1 l
LEFT JOIN myPositions99 r
ON l.Employee = r.Manager
WHERE l.Manager IS NULL
UPDATE Level2 SET Codex = COALESCE(Codex,'') + RIGHT(REPLICATE('0',5) + CONVERT(varchar(5),Level2Code),5) FROM Level2
INSERT INTO Level3 (Manager, Employee, Codex)
SELECT r.Manager, r.Employee, l.Codex
FROM Level2 l
LEFT JOIN myPositions99 r
ON l.Employee = r.Manager
WHERE r.Manager IS NOT NULL
UPDATE Level3 SET Codex = COALESCE(Codex,'') + RIGHT(REPLICATE('0',5) + CONVERT(varchar(5),Level3Code),5) FROM Level3
INSERT INTO Level4 (Manager, Employee, Codex)
SELECT r.Manager, r.Employee, l.Codex
FROM Level3 l
LEFT JOIN myPositions99 r
ON l.Employee = r.Manager
WHERE r.Manager IS NOT NULL
UPDATE Level4 SET Codex = COALESCE(Codex,'') + RIGHT(REPLICATE('0',5) + CONVERT(varchar(5),Level4Code),5) FROM Level4
-- We do the final level and check @@ROWCOUNT to show that there are no more levels
INSERT INTO Level5 (Manager, Employee, Codex)
SELECT r.Manager, r.Employee, l.Codex
FROM Level4 l
LEFT JOIN myPositions99 r
ON l.Employee = r.Manager
WHERE r.Manager IS NOT NULL
UPDATE Level5 SET Codex = COALESCE(Codex,'') + RIGHT(REPLICATE('0',5) + CONVERT(varchar(5),Level5Code),5) FROM Level5
GO
-- Using the Viewe (essentially all the work tables) update the position tree
UPDATE P SET CODEX = v.CODEX
FROM myPositions99 P JOIN myView99 v ON p.Employee = v.Employee
-- Show Me Pat's group
SELECT *
FROM myPositions99 l
LEFT JOIN myPositions99 r
ON r.Codex LIKE l.codex + '%'
AND l.Codex <> r.Codex
WHERE l.Employee = 'Pat'
-- Show me 2 levels down from the top
SELECT *
FROM myPositions99 l
LEFT JOIN myPositions99 r
ON r.Codex LIKE l.codex + '%'
AND l.Codex <> r.Codex
AND LEN(r.codex)/5 < 4
WHERE LEN(l.codex)/5 = 1
-- Show me everyones level
SELECT Employee, LEN(codex)/5, Codex AS LevelCode
FROM myPositions99
-- Add a new employee that reports to the top
DECLARE @MNGR_CODEX varchar(800), @NEW_CODEX varchar(800), @CHNG_CODEX varchar(800)
SELECT @MNGR_CODEX = RTRIM(CODEX) FROM myPositions99
WHERE Employee = 'Gerard'
SELECT @NEW_CODEX = @MNGR_CODEX+RIGHT(RTRIM('00000'+CONVERT(varchar(800),(MAX(NEW_CODEX)+1))),5)
FROM (SELECT CONVERT(int,SUBSTRING(CODEX,LEN(@MNGR_CODEX)+1,5)) AS NEW_CODEX
FROM myPositions99
WHERE CODEX LIKE @MNGR_CODEX+'%'
AND LEN(RTRIM(CODEX)) = LEN(@MNGR_CODEX)+5) AS XXX
INSERT INTO myPositions99(Manager, Employee, codex)
SELECT 'Gerard' AS Manager
, 'Mickey' AS Employee
, @NEW_CODEX
-- Show me Gerard's direct reports
SELECT * FROM myPositions99 WHERE Manager = 'Gerard'
-- Now let's move a branch....move Nicky Under Pat
-- What's the tree look like now?
SELECT codex, SPACE(LEN(codex)-5)+Employee FROM myPositions99 ORDER BY codex
GO
-- Get a new codex using Pat's codex info (The Manger) and Nicky's (The Employee)
DECLARE @MNGR_CODEX varchar(800), @NEW_CODEX varchar(800), @CHNG_CODEX varchar(800)
SELECT @MNGR_CODEX = RTRIM(CODEX) FROM myPositions99 WHERE Employee = 'Pat'
SELECT @CHNG_CODEX = RTRIM(CODEX) FROM myPositions99 WHERE Employee = 'Nick'
-- Find the New codex For Nicky, using Pat's Codex Node
SELECT @NEW_CODEX = @MNGR_CODEX+RIGHT(RTRIM('00000'+CONVERT(varchar(800),(MAX(NEW_CODEX)+1))),5)
FROM (SELECT CONVERT(int,SUBSTRING(CODEX,LEN(@MNGR_CODEX)+1,5)) AS NEW_CODEX
FROM myPositions99
WHERE CODEX LIKE @MNGR_CODEX+'%'
AND LEN(RTRIM(CODEX)) = LEN(@MNGR_CODEX)+5) AS XXX
-- Now take the New Codex and change Nicky, and change every underlying Codex Node for the rest of the employees
UPDATE P
SET CODEX = @NEW_CODEX + SUBSTRING(CODEX,LEN(RTRIM(@CHNG_CODEX))+1,800-LEN(RTRIM(@CHNG_CODEX)))
FROM myPositions99 P
WHERE CODEX LIKE @CHNG_CODEX + '%'
GO
-- What's the tree look like now?
SELECT codex, SPACE(LEN(codex)-5)+Employee FROM myPositions99 ORDER BY codex
GO
DROP VIEW myView99
DROP TABLE Level1
DROP TABLE Level2
DROP TABLE Level3
DROP TABLE Level4
DROP TABLE Level5
DROP TABLE myPositions99
GO