Select n-tier hierarchy from one table

Question

Post reply

Select n-tier hierarchy from one table

reetikashri

SSC Veteran

Points: 221
More actions
February 2, 2009 at 11:34 pm

#128441

Hi all,I need a help, I ve to write a sql query to retrieve data in n-tier hierarchy.Let me explain it with an exampleI have a table tabl1 with 3 columns: EmpID, Emp_Name, ManagerIDi want a out put that display all the hierarchy in the table like employee, boss, boss's boss etc.Here we don't know the depth.Pls reply as soon as possible

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"] Let us know...

Create Table dbo.Employee(

EmpIdInt

,EmpNameVarChar(10)

,MgrIdInt)

Go

Insert Into Employee

Select1, 'AAAA', 2 Union All

Select2, 'BBBB', 3 Union All

Select3, 'CCCC', 4 Union All

Select4, 'DDDD', 1 Union All

Select5, 'EEEE', 2

Go

SelectE.EmpId, E.EmpName, E.MgrId, M.EmpName

FromEmployee As E Inner Join Employee As M

On E.MgrId = M.EmpId

Go

Mahesh

[/font]

MH-09-AM-8694

reetikashri SSC Veteran Points: 221 More actions · Answer 2

Thnx ya

for replying but i this shows only two levels

i want it to show all the levels.

like

Big Boss | Boss | lead | ordinaly emp|

am i clear to u

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

[font="Verdana"]Let us know some sample data with expected o/p. Even the example I have given you earlier, go into its details. It is self explanatory. Anyways let us know your expected o/p

Mahesh[/font]

MH-09-AM-8694

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

see if this helps

CREATE TABLE tabl1(

EmpId INT,

Emp_Name VARCHAR(20),

ManagerID INT)

INSERT INTO tabl1(EmpId,Emp_Name,ManagerID)

SELECT 1, 'Big Boss', NULL UNION ALL

SELECT 2, 'Boss1', 1 UNION ALL

SELECT 3, 'Boss2', 1 UNION ALL

SELECT 4, 'lead1', 2 UNION ALL

SELECT 5, 'lead2', 3 UNION ALL

SELECT 6, 'ordinary emp1', 4 UNION ALL

SELECT 7, 'ordinary emp2', 4 UNION ALL

SELECT 8, 'ordinary emp3', 4 UNION ALL

SELECT 9, 'ordinary emp4', 5;

WITH CTE AS (

SELECT EmpId,Emp_Name,ManagerID, 1 AS Level, CAST(Emp_Name AS VARCHAR(MAX)) AS FullPath

FROM tabl1

WHERE ManagerID IS NULL

UNION ALL

SELECT t.EmpId,t.Emp_Name,t.ManagerID, c.Level+1, c.FullPath + '/'+CAST(t.Emp_Name AS VARCHAR(MAX))

FROM tabl1 t

INNER JOIN CTE c ON c.EmpId=t.ManagerID

)

SELECT SPACE(Level) + Emp_Name

FROM CTE

ORDER BY FullPath

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 5

I guess this is what you want....

declare @sample1-2 table

(

RecordID int Primary key NOT NULL ,

ParentRecordID int,

SortOrder int,

Description nvarchar(100),

Salary money

)

/* Start loading of test data */

insert into @sample1-2 values(1,null,null,'CEO',10)

insert into @sample1-2 values(2,1,1,'Vice Pres. Marketing',9)

insert into @sample1-2 values(3,1,2,'Vice Pres. Ops-',8)

insert into @sample1-2 values(4,2,1,'Marketing Director - Direct Mail',7)

insert into @sample1-2 values(5,2,2,'Marketing Director - TV',6)

insert into @sample1-2 values(6,1,3,'Vice Pres. - Research',5)

insert into @sample1-2 values(7,4,1,'Human Resources Director',4)

insert into @sample1-2 values(8,4,2,'Some other item',3)

insert into @sample1-2 values(9,6,1,'Research Analyst',2)

set nocount off;

with RecursionCTE (RecordID,ParentRecordID,SortOrder,Salary,TOC)

as

(

select RecordID,ParentRecordID,SortOrder,Salary,

convert(varchar(100),'') TOC

from @sample1-2

where ParentRecordID is null

union all

select R1.RecordID,

R1.ParentRecordID,

R1.SortOrder,

R1.Salary,

case when DataLength(R2.TOC) > 0

then convert(varchar(100),R2.TOC + '.'

+ cast(R1.SortOrder as varchar(10)))

else convert(varchar(100),

cast(R1.SortOrder as varchar(10)))

end as TOC

from @sample1-2 as R1

join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID

)

select * from RecursionCTE order by ParentRecordID,SortOrder asc

Regards,
Nitin

reetikashri SSC Veteran Points: 221 More actions · Answer 6

Hi

u knw i ve not cleared my requirement, actually i thought that employee- boss example will help me.

But it is not working.

I ve to design a Crystal Report and i want to show the hierarchy in toggle group tree.

my database looks like this............

here PreviousID in TblFeederRating may be EquipmentType or FeederRating.

and in report i don't want to display IDs.

hope it is some how clear now.............

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 7

[font="Verdana"]Let us know, what problem still your are facing. What stange situation you have and three of the examples are not working for you.

Thats why I have asked you for table structure with sample data and the desired o/p and conditions, if any.

Mahesh[/font]

MH-09-AM-8694