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