Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

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
0
mu_ravi1
Asked:
mu_ravi1
  • 3
  • 3
1 Solution
 
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
 
Éric MoreauSenior .Net ConsultantCommented:
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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
 
Éric MoreauSenior .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
 
mu_ravi1Author Commented:
Thanks for ur help and support
0
 
Éric MoreauSenior .Net ConsultantCommented:
A "B" grade for such a specific code!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now