Hirarchial data modification in the query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Hirarchial data modification in the query

Author

Topic

venkatkrishna
Starting Member

12 Posts

Posted - 2007-05-24 : 04:39:12

emp_id emp_name mgr_id Designation
100 Gautam NULL Manager Y
101 nishant 100 Onsite_Cordinator Y
102 manish 100 onsite-starter Y
103 krishna 102 offshore Y
104 Vikram 102 offshore Y

I have written a function which returns a table with the hirachical data. i want retutn another colums with returns the path

like
for example if u take the emp_id 103 his managerid 102 his manager id 100. i want path out put as 103,102,101. can any one

suggest how to change the function to return the path.

the following is the path.
createfunction dbo.expand2(@current varchar(2000))
returns @ctest TABLE (level int,emp_id varchar(2000),mgr_id varchar(2000),Path varchar(300))
AS
BEGIN
DECLARE @CTEST1 TABLE (level int, emp_id varchar(2000),mgr_id varchar(2000),path

varchar(300))
DECLARE @level int

DECLARE @stack_tab TABLE(item varchar(2000),level int)

INSERT INTO @stack_tab(item, level) VALUES (@current, 1)

SELECT @level = 1

WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM @stack_tab WHERE level=@level)
BEGIN
SELECT @current = item FROM @stack_tab WHERE level = @level-- AND RowNumber =

(SELECT MIN(RowNumber) from @stack_tab WHERE level = @level)
insert into @CTEST1 select @level, @current,(select mgr_id from test where

emp_id=@current),null

DELETE FROM @stack_tab WHERE level = @level AND item = @current
--update @Ctest1 set path=convert(varchar,(select emp_id from test where

emp_id=@current)) + ','+@current
INSERT @stack_tab
SELECT emp_id, @level + 1
FROM test
WHERE mgr_id = @current order by emp_id desc

IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1

END

update t set t.path=p.path+ltrim(str(t.mgr_id,6,0))+',' from @ctest1 as t
inner join @Ctest1 p on (t.mgr_id=p.emp_id) --where p.level>0-- and p.path is not null and

t.level is null

INSERT INTO @ctest SELECT level, emp_id,mgr_id,path FROM @CTEST1 order by level--group by

level

RETURN
END
--select * from dbo.expand2('100')

the output would be level emp_id,mgr_id,path
level emp_id mgr_id path
1 100 NULL Null
2 101 100 100,101
2 102 100 100,102
3 103 102 100,102,103
3 104 102 100,102,104

can any one help me to get the result.

Regards,
Krishna

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-25 : 01:23:15

Try this....
it is a batch. please execute it and if get the required result...create this as UDF

Set nocount on

Set nocount on

--data sample creation
declare @t table(emp_id int, emp_name varchar(50), mgr_id int, Designation char(80))
insert @t
select 100, 'Gautam', NULL ,'Manager Y 'union all
select 101, 'nishant', 100 ,'Onsite_Cordinator Y' union all
select 102, 'manish', 100 ,'onsite-starter Y' union all
select 103, 'krishna', 102, 'offshore Y' union all
select 104, 'Vikram', 102 ,'offshore Y' union all
select 105, 'Vikram', NULL ,'offshore Y'

--Actual batch
declare @tt table (id int identity(1,1), emp_id int, mgr_id int, level int, path varchar(300))
Insert @tt(emp_id, mgr_id) select emp_id, mgr_id from @t
declare @mgr int, @cnt int, @index int, @lvl int, @emp int
declare @path varchar(300)

Set @cnt = scope_identity()
Set @index = 1

While @index <= @cnt
Begin
Select @mgr = mgr_id, @emp = emp_id from @tt where id = @index
Set @lvl = 1
If @mgr is null
Set @path = @mgr
Else
Set @path = cast(@emp as varchar(20))
While @mgr is Not NULL
Begin
Set @path = @path + ',' + cast(coalesce(@mgr, '') as varchar(50))
Select @mgr = mgr_id from @tt where emp_id = @mgr
Set @lvl = @lvl + 1
End
Update @tt set level = @lvl,
path = @path
where emp_id = @emp
Set @index = @index + 1
End
Select level, emp_id, mgr_id, path from @tt

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-25 : 05:06:50

Also refer
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

venkatkrishna
Starting Member

12 Posts

Posted - 2007-05-28 : 08:40:37

quote:
Originally posted by pbguy

Try this....
it is a batch. please execute it and if get the required result...create this as UDF

Set nocount on

Set nocount on

--data sample creation
declare @t table(emp_id int, emp_name varchar(50), mgr_id int, Designation char(80))
insert @t
select 100, 'Gautam', NULL ,'Manager Y 'union all
select 101, 'nishant', 100 ,'Onsite_Cordinator Y' union all
select 102, 'manish', 100 ,'onsite-starter Y' union all
select 103, 'krishna', 102, 'offshore Y' union all
select 104, 'Vikram', 102 ,'offshore Y' union all
select 105, 'Vikram', NULL ,'offshore Y'

--Actual batch
declare @tt table (id int identity(1,1), emp_id int, mgr_id int, level int, path varchar(300))
Insert @tt(emp_id, mgr_id) select emp_id, mgr_id from @t
declare @mgr int, @cnt int, @index int, @lvl int, @emp int
declare @path varchar(300)

Set @cnt = scope_identity()
Set @index = 1

While @index <= @cnt
Begin
Select @mgr = mgr_id, @emp = emp_id from @tt where id = @index
Set @lvl = 1
If @mgr is null
Set @path = @mgr
Else
Set @path = cast(@emp as varchar(20))
While @mgr is Not NULL
Begin
Set @path = @path + ',' + cast(coalesce(@mgr, '') as varchar(50))
Select @mgr = mgr_id from @tt where emp_id = @mgr
Set @lvl = @lvl + 1
End
Update @tt set level = @lvl,
path = @path
where emp_id = @emp
Set @index = @index + 1
End
Select level, emp_id, mgr_id, path from @tt

This worked well but it if i want to have to specific manger or emp. for example if i give 100 as a parameter. it should return all comes under 100. here all the records come. if i give 102.it should return 102 at level 1 and 103,104 at level 2.with path. can u help on the same.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 01:21:01

Try this and optimize wherever required

--data sample creation
declare @t table(emp_id int, emp_name varchar(50), mgr_id int, Designation char(80))
insert @t
select 100, 'Gautam', NULL ,'Manager Y 'union all
select 101, 'nishant', 100 ,'Onsite_Cordinator Y' union all
select 102, 'manish', 100 ,'onsite-starter Y' union all
select 103, 'krishna', 102, 'offshore Y' union all
select 104, 'Vikram', 102 ,'offshore Y' union all
select 105, 'Visu', 104 ,'offshore Y' union all
select 106, 'Raja', 105 ,'offshore Y'
--Actual batch
declare @tt table (id int identity(1,1), emp_id int, mgr_id int, level int, path varchar(300))
declare @mgr int, @cnt int, @index int, @lvl int, @emp int, @input int, @initial_lvl int
declare @path varchar(300), @casted_input varchar(10)

Set @input = 106
Set @index = 1
Insert @tt(emp_id, mgr_id) select emp_id, mgr_id from @t
Set @casted_input = Cast(@input as varchar(10))
Set @cnt = scope_identity()
While @index <= @cnt
Begin
Select @mgr = mgr_id, @emp = emp_id from @tt where id = @index
Set @lvl = 1
If @mgr is null
Set @path = @mgr
Else
Set @path = cast(@emp as varchar(20))

While @mgr is not null
Begin
Set @path = @path + ',' + cast(coalesce(@mgr, '') as varchar(50))
Select @mgr = mgr_id from @tt where emp_id = @mgr
Set @lvl = @lvl + 1
End
Update @tt set level = @lvl,
path = @path
where emp_id = @emp
Set @index = @index + 1
End

Select @initial_lvl = level from @tt where emp_id = @input
Select Level = case when level > 1 then level - (@initial_lvl -1) else level end, emp_id, mgr_id, Path = left(path, (patindex('%,' + @casted_input + ',%', ',' + path + ',') + Len(@casted_input)- 1)) from @tt where
',' + path + ',' like '%,' + @casted_input + ',%' or @input = (Select emp_id from @tt where mgr_id is null)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-29 : 01:25:59

If you are doing this in SQL Server 2005, it is very simple to use CTE feature for retrieving hierarchical data like this.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources