how to the hirarchial data using sql query.

My table called employee contains the following data.
empid  empname jobname managerid
1        ravi   se       3

2        kumar  SSe      3

3        xyz    TeamLead 4

4        ppp    ProjectLead 5

5        MMM    Manager 6

6        ccc     CEo

7        ram     Fresher 2        
   




i need the output as follwors.

The input id mgrid.
 
i need the output in the hirarichael manner.
manager
projectlead
teamlead
sse
se
fresher
mu_ravi1Asked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
use this:

declare @employee table 
  ( 
      empid int  Primary key NOT NULL , 
      managerid int, 
      empname nvarchar(100), 
      jobname nvarchar(100)
   )

insert into @employee( empid, managerid, empname, jobname )

select 1, 3, 'ravi', 'se'
union
select 2, 3, 'kumar', 'SSe'
union
select 3, 4, 'xyz', 'TeamLead'
union
select 4, 5, 'ppp', 'ProjectLead'
union
select 5, 6, 'MMM', 'Manager'
union
select 6, null, 'ccc', 'CEo'
union
select 7, 2, 'ram', 'Fresher'



SELECT 'original data', * FROM @employee;

with RecursionCTE (empID,managerid, empname, jobname) 
 as
 (
   select empid,managerid,empname, jobname 
      from @employee
      where managerid is null
   union all
   select R1.empid,
          R1.managerid,
          R1.empname,
          R1.jobname
      from @employee as R1
     join RecursionCTE as R2 on R1.managerid = R2.empid
  )

select 'new data', * from RecursionCTE 

Open in new window

0
 
Priya PerumpilavilSoftware EngineerCommented:
select * from table1 order by managerid desc

0
 
mu_ravi1Author Commented:
Hi Priya

i need to show the Hirarichal levels to top to bottom.
Above u have mentioned a simple query.
my thought of applying collase on the query to the level to level data in the output
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Éric MoreauSenior .Net ConsultantCommented:
0
 
mu_ravi1Author Commented:
Hi Emoreau,

Could please give me the code or qurey for my requirement.
it U do that it would be highly appreciable and it is a great help for me
Ravi
0
 
mu_ravi1Author Commented:
Thanks for ur help and support
0
 
Éric MoreauSenior .Net ConsultantCommented:
A "B" grade for such a specific code!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.