Create Manager Hierarchy for Organization in SSMS

Today we will explore on Manager hierarchy level. To do this first we have to create a source table. Let’s assume the source table having columns with following data.

Create Script:

CREATE TABLE [dbo].[ManagerHierarchy](

       [EmpID] [int] NULL,

       [EmpName] [varchar](200) NULL,

       [ManName] [varchar](200) NULL,

       [manID] [int] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[ManagerHierarchy] ([EmpID], [EmpName], [ManName], [manID]) VALUES (1, N'Subhadip', NULL, NULL)

GO

INSERT [dbo].[ManagerHierarchy] ([EmpID], [EmpName], [ManName], [manID]) VALUES (2, N'Pritam', N'Subhadip', 1)

GO

INSERT [dbo].[ManagerHierarchy] ([EmpID], [EmpName], [ManName], [manID]) VALUES (3, N'tanmoy', N'Subhadip', 1)

GO

INSERT [dbo].[ManagerHierarchy] ([EmpID], [EmpName], [ManName], [manID]) VALUES (4, N'Sourav', N'Pritam', 2)

GO

INSERT [dbo].[ManagerHierarchy] ([EmpID], [EmpName], [ManName], [manID]) VALUES (5,

N'Titas', N'Tanmoy', 3)

GO

INSERT [dbo].[ManagerHierarchy] ([EmpID], [EmpName], [ManName], [manID]) VALUES (6, N'Anurag', N'Sourav', 4)

GO

Now we have to format the data from coming from source table. Here we will form the in below format.

Empname, EmpID, ReportsToMan, ReportsToManID, ManPath, EmpLevel

To make above format we have write CTE (Common Table Expression) on top of the source table.

SQL Script:

;WITH EmployeeSet(Empname, EmpID, ReportsToMan, ReportsToManID, ManPath, EmpLevel)

AS

(SELECT

      EmpName,

      EmpID, 

      ManName,

      manID,

      cast((EmpName + ',') as varchar(2000)),

       0

    FROM [ManagerHerarchy] AS e

    WHERE EmpName = 'Subhadip'

    UNION ALL

    SELECT

              e.EmpName,

        e.EmpID,    

        e.ManName,

        e.manID,

           cast(d.ManPath + (e.EmpName + ',') as varchar(2000)),

        d.EmpLevel + 1

       

    FROM [ManagerHerarchy] AS e

              JOIN EmployeeSet AS d ON e.ManID = d.EmpID

)

select * FROM EmployeeSet

Now we have to store the CTE to a Temp table.

SQL Query:

select * into #tempTbl FROM EmployeeSet

Now the challenge is to split the string value of ManPath column and put each value to a column (Ex: level 1, level 2 etc.)

For simplicity, we have to create a function for split.

SQL Script:

CREATE FUNCTION [SplitFunction](@InputStr varchar(max))         

returns @outputtable TABLE (items varchar(max))         

as         

begin         

    declare @idx int   

    declare @Delimiter varchar(max)     

    declare @slice varchar(max)         

    set @Delimiter = ','    

    select @idx = 1         

        if len(@InputStr)<1 or @InputStr is null  return         

       

    while @idx!= 0         

    begin         

        set @idx = charindex(@Delimiter,@InputStr)         

        if @idx!=0         

            set @slice = left(@InputStr,@idx - 1)         

        else         

             set @slice = @InputStr         

            

        if(len(@slice)>0)    

            insert into @outputtable(Items) values(LTRIM(RTRIM(REPLACE(REPLACE(@slice,CHAR(9),''),CHAR(13)+CHAR(10),''))))         

    

        set @InputStr = right(@InputStr,len(@InputStr) - @idx)         

        if len(@InputStr) = 0 break         

    end     

return         

end

Now we have to use this above function to get the string value into column format.

SQL Script:

SELECT EmpName,

       ReportsToMan,

          EmpLevel,

ISNULL((SELECT items FROM (select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Position,  items from [dbo].[SplitInputStrToColumn] (A.ManPath)) S where Position = 1)  ,'' )AS Level1,

ISNULL((SELECT items FROM (select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Position,  items from [dbo].[SplitInputStrToColumn] (A.ManPath)) S where Position = 2)  ,'' )AS Level2,

ISNULL((SELECT items FROM (select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Position,  items from [dbo].[SplitInputStrToColumn] (A.ManPath)) S where Position = 3)  ,'' )AS Level3,

ISNULL((SELECT items FROM (select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Position,  items from [dbo].[SplitInputStrToColumn] (A.ManPath)) S where Position = 4)  ,'' )AS Level4

from #tempTbl A

Now we can store the output into Manager Hierarchy Level table.

How does SplitInputStrToColumn work?

First we have look on the Output of SplitInputStrToColumn Function.

Step1:

Select * from [SplitInputStrToColumn] ('Pritam,Das,Kantamoni')

Output:

Step2:

Now our aim should be, put this spitted values into defined columns. First row will move to Level1 Column, second row will move to Level2 Column and so on. To implement like this, we have to apply row number before the output of SplitInputStrToColumn Function.

Select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as Position ,items from [SplitInputStrToColumn] ('Pritam,Das,Kantamoni')

Output:

Step3:

Based on Position we have to filter the spitted string values.

select Position,items from (Select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as Position ,items from [SplitInputStrToColumn] ('Pritam,Das,Kantamoni'))b where b.Position=1

Output: