Hierarchy - How to retrieve hierarchical data from SQL table

Questions

I have 2 tables : T_Employees and T_Projects

Every project has different number of employees assigned. What i need to do, is to get hierarchical structure of each employee, assigned to a specific project.

Look at the image below and the expected results.

How to get this done on Microsoft SQL Server 2008R2?

https://i.stack.imgur.com/8hHoG.png https://i.stack.imgur.com/8hHoG.png

CREATE TABLE [dbo].[T_Projects](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ProjectId] [int] NOT NULL,
    [EmployeeId] [int] NOT NULL,
    CONSTRAINT [PK_T_Projects] PRIMARY KEY CLUSTERED([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] 
GO

SET IDENTITY_INSERT [dbo].[T_Projects] ON
INSERT [dbo].[T_Projects] ([ID], [ProjectId], [EmployeeId]) VALUES (1, 456,10)
INSERT [dbo].[T_Projects] ([ID], [ProjectId], [EmployeeId]) VALUES (2, 456, 12)
INSERT [dbo].[T_Projects] ([ID], [ProjectId], [EmployeeId]) VALUES (3, 23, 11)
SET IDENTITY_INSERT [dbo].[T_Projects] OFF

CREATE TABLE [dbo].[T_Employees](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Level] [int] NOT NULL,
    [Employee] [nvarchar](256) NOT NULL,
    [Department] [nvarchar](5) NOT NULL,
    [MasterId] [int] NULL,
    [Code] [nvarchar](10) NOT NULL,
    [Note] [nvarchar](100) NULL,
    CONSTRAINT [PK_T_Employees] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[T_Employees] ON
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (1, 1, N Thomas S. , N A , NULL, N 1-4 , NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (2, 1, N Michael F. , N A , NULL, N 1-5 , NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (3, 1, N Simone S. , N A , NULL, N 1-3 , NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (4, 2, N Stefan K. , N B , 1, N 2-18 , NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (5, 2, N Mike T. , N B , 2, N 2-96 , NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (6, 2, N Loris P. , N B , 3, N 2-15 , NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (7, 3, N Lennon I. , N B , 4, N 2-19 , NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (8, 3, N Kerim K. , N C , 4, N 2-66 , NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (9, 3, N Ilmas Y. , N C , 6, N 2-59 , NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (10, 4, N Innes Y. , N D , 8, N 3-89 , NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (11, 4, N Andreas U. , N E , 7, N 3-63 , NULL)
INSERT [dbo].[T_Employees] ([ID], [Level], [Employee], [Department], [MasterId], [Code], [Note]) VALUES (12, 4, N Fatih O. , N I , 9, N 3-32 , NULL)
SET IDENTITY_INSERT [dbo].[T_Employees] OFF

Answers

You can travers a hierarchy with a recursive CTE

This is the classical top down CTE:

WITH EmployeesHierarchy AS
(
    SELECT ID,[Level],Employee,Department,Code,MasterId
    FROM T_Employees
    WHERE [Level]=1
    UNION ALL
    SELECT nextLevel.ID,nextLevel.[Level],nextLevel.Employee,nextLevel.Department,nextLevel.Code,nextLevel.MasterId
    FROM EmployeesHierarchy AS recCall
    INNER JOIN T_Employees AS nextLevel ON nextLevel.[Level]=recCall.[Level]+1 AND nextLevel.MasterId=recCall.ID
)
SELECT * FROM EmployeesHierarchy
ORDER BY [Level],MasterId
GO

And now the other way round: I start with the employees mentioned in the project and move up the list until there is no parentId any more. The Project s data gotten in the first part of the CTE are just passed through to show up in all rows.

WITH EmployeesHierarchy AS
(
    SELECT p.ID AS p_ID,p.ProjectId,e.ID AS e_ID,[Level],e.Employee,e.Department,e.Code,e.MasterId
    FROM T_Projects AS p 
        INNER JOIN T_Employees AS e ON p.EmployeeId=e.ID
    UNION ALL
    SELECT recCall.p_ID,recCall.ProjectId,nextLevel.ID,nextLevel.[Level],nextLevel.Employee,nextLevel.Department,nextLevel.Code,nextLevel.MasterId
    FROM EmployeesHierarchy AS recCall
    INNER JOIN T_Employees AS nextLevel ON nextLevel.ID=recCall.MasterId
)
SELECT * FROM EmployeesHierarchy
--WHERE ProjectId=456
ORDER BY [Level]

The result

+------+-----------+------+-------+------------+------------+------+----------+
| p_ID | ProjectId | e_ID | Level | Employee   | Department | Code | MasterId |
+------+-----------+------+-------+------------+------------+------+----------+
| 3    | 23        | 1    | 1     | Thomas S.  | A          | 1-4  | NULL     |
+------+-----------+------+-------+------------+------------+------+----------+
| 2    | 456       | 3    | 1     | Simone S.  | A          | 1-3  | NULL     |
+------+-----------+------+-------+------------+------------+------+----------+
| 1    | 456       | 1    | 1     | Thomas S.  | A          | 1-4  | NULL     |
+------+-----------+------+-------+------------+------------+------+----------+
| 2    | 456       | 6    | 2     | Loris P.   | B          | 2-15 | 3        |
+------+-----------+------+-------+------------+------------+------+----------+
| 1    | 456       | 4    | 2     | Stefan K.  | B          | 2-18 | 1        |
+------+-----------+------+-------+------------+------------+------+----------+
| 3    | 23        | 4    | 2     | Stefan K.  | B          | 2-18 | 1        |
+------+-----------+------+-------+------------+------------+------+----------+
| 3    | 23        | 7    | 3     | Lennon I.  | B          | 2-19 | 4        |
+------+-----------+------+-------+------------+------------+------+----------+
| 1    | 456       | 8    | 3     | Kerim K.   | C          | 2-66 | 4        |
+------+-----------+------+-------+------------+------------+------+----------+
| 2    | 456       | 9    | 3     | Ilmas Y.   | C          | 2-59 | 6        |
+------+-----------+------+-------+------------+------------+------+----------+
| 1    | 456       | 10   | 4     | Innes Y.   | D          | 3-89 | 8        |
+------+-----------+------+-------+------------+------------+------+----------+
| 2    | 456       | 12   | 4     | Fatih O.   | I          | 3-32 | 9        |
+------+-----------+------+-------+------------+------------+------+----------+
| 3    | 23        | 11   | 4     | Andreas U. | E          | 3-63 | 7        |
+------+-----------+------+-------+------------+------------+------+----------+

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/36838003/how-to-retrieve-hierarchical-data-from-sql-table

Related