Hierarchical working... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Hierarchical working...

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2013-11-04 : 07:08:41

i am working on Attendance System i want to work on hierarchy Structure
Boss EmployeeID is Employee ID of First Immediate Boss.

just like Employee ID 5 HAVE 4 BOSS's EmployeeID's of Bosses are 4,3,2,1

My Date is like this
Employee ID Name Boss Employee ID
1 ABC NULL
2 DEF 1
3 GHI 2
4 JKL 3
5 MNO 4

11 ABC NULL
12 1DEF 11
13 1GHI 12
14 1JKL 13
15 1MNO 14

i want to Display Subornates of Every Boss
my required data is like this

Sub Ordinates
Employee ID Name Subornates ID
1 ABC 2,3,4,5
2 DEF 3,4,5
3 GHI 4,5
4 JKL 5
5 MNO NULL

11 ABC 12,13,14
12 1DEF 13,14,15
13 1GHI 14,15
14 1JKL 15
15 1MNO NULL

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 07:21:44

[code]

declare @emp table
(
EmployeeID int,
Name varchar(5),
BossEmployeeID int
)
insert @emp
values(1,'ABC', NULL),
(2, 'DEF', 1),
(3, 'GHI', 2),
(4, 'JKL', 3),
(5, 'MNO', 4),

(11, 'ABC' ,NULL),
(12, '1DEF', 11),
(13, '1GHI', 12),
(14, '1JKL', 13),
(15, '1MNO', 14)

;With OrgHierarchy
AS
(
SELECT t.[EmployeeID],t.Name ,t.BossEmployeeID, CAST(NULL AS varchar(max)) AS [BossesID]
FROM @emp t
WHERE NOT EXISTS (SELECT 1
FROM @emp WHERE [BossEmployeeID] = t.[EmployeeID])
UNION ALL
SELECT t.[EmployeeID],t.[Name],t.BossEmployeeID,CAST(CAST(oh.[EmployeeID] AS varchar(10)) +COALESCE( ',' + oh.[BossesID],'') AS varchar(max))
FROM OrgHierarchy oh
INNER JOIN @emp t
ON t.[EmployeeID] = oh.[BossEmployeeID]
)

SELECT [EmployeeID],[Name],[BossesID]
FROM OrgHierarchy
ORDER BY [EmployeeID]
OPTION (MAXRECURSION 0)

output
-----------------------------------
EmployeeID Name BossesID
-----------------------------------
1 ABC 2,3,4,5
2 DEF 3,4,5
3 GHI 4,5
4 JKL 5
5 MNO NULL
11 ABC 12,13,14,15
12 1DEF 13,14,15
13 1GHI 14,15
14 1JKL 15
15 1MNO NULL

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources