Department Hierarchy

  • Hi all,

    how do i arrange the department and sections if i have a below table structure and data

    table

    dept_id Dept_Name Sec_id

    1 A1 0

    2 A2 1

    3 B1 1

    4 A3 2

    5 A4 2

    6 B2 1

    7 B3 5

    8 B4 5

    9 B5 6

    10 A5 3

    output

    dept_id dept_name

    1 A1

    2 A2

    4 A3

    5 A4

    7 B3

    8 B4

    3 B1

    10 A5

    6 B2

    9 B5

    in simple ways i want to achieve parent and child relationship in the departments.

    any help is highly appreciated.

    regards

  • Quick solution utilizing a recursive CTE, fine for small sets but I suggest you have a look at Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/url] and Joe Celko's Trees and Hierarchies in SQL for Smarties

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID(N'dbo.TBL_DEPARTMENT_HIERARCHY') IS NOT NULL DROP TABLE dbo.TBL_DEPARTMENT_HIERARCHY;

    CREATE TABLE dbo.TBL_DEPARTMENT_HIERARCHY

    (

    dept_id INT NOT NULL CONSTRAINT PK_DBO_TBL_DEPARTMENT_HIERARCHY_DEPT_ID PRIMARY KEY CLUSTERED

    ,Dept_Name VARCHAR(10) NOT NULL CONSTRAINT UNQ_DBO_TBL_DEPARTMENT_HIERARCHY_DEPT_NAME UNIQUE

    ,Sec_id INT NULL CONSTRAINT FK_DBO_TBL_DEPARTMENT_HIERARCHY_SEC_ID_DBO_TBL_DEPARTMENT_HIERARCHY_DEPT_ID

    FOREIGN KEY REFERENCES dbo.TBL_DEPARTMENT_HIERARCHY(dept_id)

    );

    INSERT INTO dbo.TBL_DEPARTMENT_HIERARCHY(dept_id,Dept_Name,Sec_id)

    VALUES

    (1 ,'A1',NULL)

    ,(2 ,'A2',1)

    ,(3 ,'B1',1)

    ,(4 ,'A3',2)

    ,(5 ,'A4',2)

    ,(6 ,'B2',1)

    ,(7 ,'B3',5)

    ,(8 ,'B4',5)

    ,(9 ,'B5',6)

    ,(10,'A5',3);

    ;WITH DEP_HIERARCHY AS

    (

    SELECT

    TDH.dept_id

    ,TDH.Dept_Name

    ,TDH.Sec_id

    ,1 AS LEVEL

    ,CONVERT(VARCHAR(500),TDH.Dept_Name + '\',0) AS HIER_STR

    FROM dbo.TBL_DEPARTMENT_HIERARCHY TDH

    WHERE TDH.Sec_id IS NULL

    UNION ALL

    SELECT

    TDH.dept_id

    ,TDH.Dept_Name

    ,TDH.Sec_id

    ,DH.LEVEL + 1

    ,CONVERT(VARCHAR(500),DH.HIER_STR + CONVERT(VARCHAR(10),TDH.Dept_Name + '\',0),0) AS HIER_STR

    FROM DEP_HIERARCHY DH

    INNER JOIN dbo.TBL_DEPARTMENT_HIERARCHY TDH

    ON DH.dept_id = TDH.Sec_id

    )

    SELECT

    DH.dept_id

    ,DH.Dept_Name

    ,DH.Sec_id

    ,DH.LEVEL

    ,DH.HIER_STR

    FROM DEP_HIERARCHY DH;

    ;

    Results

    dept_id Dept_Name Sec_id LEVEL HIER_STR

    ----------- ---------- ----------- ----------- --------------

    1 A1 NULL 1 A12 A2 1 2 A1\A23 B1 1 2 A1\B16 B2 1 2 A1\B29 B5 6 3 A1\B2\B510 A5 3 3 A1\B1\A54 A3 2 3 A1\A2\A35 A4 2 3 A1\A2\A47 B3 5 4 A1\A2\A4\B38 B4 5 4 A1\A2\A4\B4

  • Borrowing on Eirikur's test data, the Hierarchical Path and the hLevel can be used to format the output a bit (look at the Dept_Name column) and sort the hierarchy as you might expect like the following...

    dept_id Sec_id Dept_Name hLevel HierarchicalPath

    ----------- ----------- -------------------- ----------- --------------------

    1 NULL A1 1 \A1

    2 1 A2 2 \A1\A2

    4 2 A3 3 \A1\A2\A3

    5 2 A4 3 \A1\A2\A4

    7 5 B3 4 \A1\A2\A4\B3

    8 5 B4 4 \A1\A2\A4\B4

    3 1 B1 2 \A1\B1

    10 3 A5 3 \A1\B1\A5

    6 1 B2 2 \A1\B2

    9 6 B5 3 \A1\B2\B5

    Here's the code to do that...

    WITH

    cteDeptHierarchy AS

    (

    SELECT dept_id, Sec_id, Dept_Name, hLevel = 1,

    HierarchicalPath = CAST('\'+CAST(Dept_Name AS VARCHAR(10)) AS VARCHAR(8000))

    FROM dbo.TBL_DEPARTMENT_HIERARCHY

    WHERE Sec_id IS NULL

    UNION ALL

    SELECT e.dept_id, e.Sec_id, e.Dept_Name, hLevel = d.hLevel + 1,

    HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.Dept_Name AS VARCHAR(10)) AS VARCHAR(8000))

    FROM dbo.TBL_DEPARTMENT_HIERARCHY e

    INNER JOIN cteDeptHierarchy d ON e.Sec_id = d.dept_id

    )

    SELECT dept_id,

    Sec_id,

    Dept_Name = CAST(SPACE((hLevel-1)*2) + Dept_Name AS VARCHAR(20)), --CAST for formatting only

    hLevel,

    HierarchicalPath = CAST(HierarchicalPath AS VARCHAR(20)) --CAST for formatting only

    FROM cteDeptHierarchy

    ORDER BY HierarchicalPath

    ;

    To find out more about how the code works, see the following article.

    http://www.sqlservercentral.com/articles/T-SQL/72503/

    As Eirikur also suggested, if you end up with more than just a few rows in your hierarchy, consider the use of "hybrid" hierarchies that offer the best of Adjacency Lists, Hierarchical Paths, and Nested Sets. For a "How to" there, please see the following article.

    http://www.sqlservercentral.com/articles/Hierarchy/94040/

    Also, consider NOT using "TBL_" to prefix table names nor all caps for table names unless your whole server is case sensitive. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply