ansaurus

Question

Tough T-SQL to display org chart (hierarchy / recursion)

Answer 1

+4 A:

Use a recursive query. MSDN's article on the subject uses an example that looks similar to yours. In your case, you would select Kevin's entry as the anchor definition. Try this (completely untested):

CREATE PROCEDURE [dbo].[rptContactsHierarchy]
@ContactID varchar(100)='All'
AS
BEGIN
    WITH ManagerEmployee (ManagerID, EmployeeID, first_name, last_name, title)
    AS
    (
        -- Anchor
        SELECT ManagerID, EmployeeID, first_name, last_name, title
          FROM Contacts
         WHERE EmployeeID = @ContactID

        UNION ALL

        -- Recursion
        SELECT ManagerID, EmployeeID, first_name, last_name, title
          FROM Contacts c
          JOIN ManagerEmployee me ON (me.EmployeeID = c.ManagerID)
    )
    SELECT ManagerID,
           EmployeeID,
           first_name + ' ' + last_name AS EmployeeName,
           title as Title
    FROM ManagerEmployee
END

Marcelo Cantos 2010-07-22 13:47:04

@Marcelo Cantos - I've read through that a couple of times, is there any way you could use my query and show me how to do something like that. I'm not T-SQL savvy unfortunately :(

2010-07-22 14:13:25

I've amended the answer with my best guess at the SQL you need.

Marcelo Cantos 2010-07-22 14:27:09

@Marcelo - Definitely `UNION ALL` is required in recursive CTEs not `UNION` and the second level you will need to qualify the column names to avoid an ambiguous name error from the join.

Martin Smith 2010-07-22 14:28:23

Thanks @Martin. Fixed.

Marcelo Cantos 2010-07-22 14:29:50

@Marcelo Cantos - That definately won't work as the managerid and employeeid do not exist in one call in the select. You have to inner join back to the same table to actually get the id field

2010-07-22 14:38:55

Answer 2

+3 A:

EDIT based on OP's table:

here is an example using the columns from the OP's table definition, my sample data is as follows:

              1-Jerome
                |
              2-Joe
            /       \
     3-Paul          6-David
    /      \            /    \
 4-Jack  5-Daniel   7-Ian    8-Helen


--I only included the needed columns from the OP's table here
DECLARE @Contacts table (id varchar(36), first_name varchar(100), reports_to_id varchar(36))
INSERT @Contacts VALUES ('1','Jerome', NULL )
INSERT @Contacts VALUES ('2','Joe'   ,'1')
INSERT @Contacts VALUES ('3','Paul'  ,'2')
INSERT @Contacts VALUES ('4','Jack'  ,'3')
INSERT @Contacts VALUES ('5','Daniel','3')
INSERT @Contacts VALUES ('6','David' ,'2')
INSERT @Contacts VALUES ('7','Ian'   ,'6')
INSERT @Contacts VALUES ('8','Helen' ,'6')

DECLARE @Root_id  char(4)

--get complete tree---------------------------------------------------
SET @Root_id=null
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree


--get all below 2---------------------------------------------------
SET @Root_id=2
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree

--get all below 6---------------------------------------------------
SET @Root_id=6
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree

OUTPUT:

@Root_id=null
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
1      Jerome     NULL          NULL       NULL               1
2      Joe        1             1          Jerome             2
3      Paul       2             2          Joe                3
6      David      2             2          Joe                3
7      Ian        6             6          David              4
8      Helen      6             6          David              4
4      Jack       3             3          Paul               4
5      Daniel     3             3          Paul               4

(8 row(s) affected)

@Root_id='2   '
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
2      Joe        1             1          Jerome             1
3      Paul       2             2          Joe                2
6      David      2             2          Joe                2
7      Ian        6             6          David              3
8      Helen      6             6          David              3
4      Jack       3             3          Paul               3
5      Daniel     3             3          Paul               3

(7 row(s) affected)

@Root_id='6   '
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
6      David      2             2          Joe                1
7      Ian        6             6          David              2
8      Helen      6             6          David              2

(3 row(s) affected)

KM 2010-07-22 14:28:39

@KM - I posted my table definition in an edit to this question. I appreciate your help but I tried your example but cant seem to apply it to mine. Can you look at my table defition (the only thing relevant is basically the name field and the id, notice the join to the table itself). Thanks again KM!

2010-07-22 14:46:47

@KM - Now that's awesome thank you sir.

2010-07-22 16:00:50

ansaurus

tags:

views:

answers:

Tough T-SQL to display org chart (hierarchy / recursion)

Edit Portion

Solution

related questions