Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
hi friends,I need to write a sql that generate the hierarchy in an organization.Below an exampleemplid empname supervisor_id superv_name1 subu null null2 vid 1 sub3 ram 4 satis4 satis 2 vidi need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.whoever joinng new to org the hempid empname supervisor_id superv_name3 ram 4 satis4 satis 2 vid2 vid 1 subu4 satis 1 subu3 ram 1 subu3 ram 2 vid5 kumar 1 subu5 kumar 4 satis5 kumar 2 vid1 subu null nullThanks n advance...
bitsmed
Aged Yak Warrior
545 Posts
Posted - 2014-06-08 : 15:21:02
Something like this perhaps:
with cte as (select * from yourtable union all select a.emplid ,a.empname ,b.supervisor_id ,b.superv_name from cte as a inner join yourtable as b on b.emplid=a.supervisor_id where a.supervisor_id is not null and b.supervisor_id is not null )select * from cte
btechrames
Starting Member
3 Posts
Posted - 2014-06-10 : 00:53:01
Hi Friends..Is any other method without using cte?please
gbritton
Master Smack Fu Yak Hacker
2780 Posts
Posted - 2014-06-10 : 08:48:24
bitsmed's method uses a recursive cte. That's a nice, succinct approach. If you don't want that, use a series of left joins. The only hitch is you need to know in advance how many levels the organization has. Basically you need a left join per level (+|1-)something like:
select * from yourtable level0left join (select empname from yourtable) level1on level0.supervisor_id = level1.emplidleft join (select empname from yourtable) level2on level1.supervisor_id = level2.emplid...etc...
btw, sometimes recursive ctes are bad performers. They sometimes generate O(n^m) execution plans, where n is the number of rows in your base table, and m is the recursion depth needed to get the job done. You have to look at the plans and measure the performance. However, they are nice and simple to write!