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:
