x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Hierarchies with a twist...rocks, no salt

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

 

Print | posted on Monday, November 13, 2006 4:36 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Hierarchies with a twist...rocks, no salt

The script doesn't seem to work too well... Even after cleaning up all of the errors I can't get it to produce anything that resembles a tree. Can you post a working version?
11/13/2006 9:25 PM | Adam Machanic
Gravatar

# re: Hierarchies with a twist...rocks, no salt

Brett -- instead of Level1, Level2, etc -- consider just a simple Level table with a "LevelNumber" column that is part of the PK. This will allow for as many levels as you need, less tables and indexes, a shorter script, and so on. Of course, then instead of SELECT * FROM LEVEL2 you would do SELECT * FROM LEVEL WHERE LEVELNUMBER=2 and so on....
In other words -- normalize ! :)
11/14/2006 8:34 AM | Jeff
Gravatar

# re: Hierarchies with a twist...rocks, no salt

The script doesn't work? I'll have to 2x check it...And Jeff, the "Level" tables are only work tables and are not part of the final process....it's all about the CODEX column...I'll revisit and make 100% sure that the script will run if you cut and paste it...
11/14/2006 9:14 AM | Brett
Gravatar

# re: Hierarchies with a twist...rocks, no salt

I think using CTE's may be much easier..
11/14/2006 1:02 PM | Siva
Gravatar

# re: Hierarchies with a twist...rocks, no salt

Well CTE's may be, but if you are in 2k, then it's not an option. And in DB2v& OS/390 they don't have that either, which is where this is being applied. I have heard though that CTE's can be very pig like. In any case the script is fixed.
11/14/2006 2:36 PM | Brett
Gravatar

# re: Hierarchies with a twist...rocks, no salt

Hi Brett,

I see what you're doing now. This is called an "enumerated path" representation. Itzik Ben-Gan also calls this "materialized path". Do a Google search, or check out Celko's "Trees and Hierarchies" book or Ben-Gan and Moreau's "Advanced T-SQL For SQL Server 2000" book. Or, early next year, my book, "Expert SQL Server 2005 Development" :)

I also have an article here that shows (among other things) how to create a materialized path from an adjacency list in SQL Server 2005 using CTEs:

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1107414,00.html?FromTaxonomy=/pr/301329
11/20/2006 5:16 PM | Adam Machanic
Gravatar

# re: Hierarchies with a twist...rocks, no salt

This code is horrible.
12/16/2006 9:29 AM | John Dekran
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET