PFB code
create table tab1 (id int, rivername varchar(100), parentid int)
insert into tab1 (id, rivername, parentid)
values (1,'r1',null),
(3,'r2',null),
(2,'r3',null),
(4,'akash',3),
(5,'xyz',3),
(6,'r5',null),
(7,'abc',3),
(8,'hkjd',4),
(10,'ksjdf',7),
(19,'ksjdf',7),
(14,'jjj',null)
Alter Function udt_getHirarcy(@Iid Varchar(100))
Returns Varchar(100)
AS
Begin
Declare @Return varchar(100)=@Iid ,@Gid varchar(30) =@Iid ,@tempParentid varchar(20)
while(1=1)
Begin
select @Return = case when parentid is null then @Iid+ '.0'
else convert(varchar,parentid) + replicate ('0', 3 - len(convert(varchar,parentid))) end + @Return
,@tempParentid=parentid from tab1 where id = @Iid
if @@ROWCOUNT < 1 or @tempParentid is null
Break
select @Iid = @tempParentid
END
Return @Return
END
GO
select convert(float,dbo.udt_getHirarcy(id)) hie,* from tab1
order by hie
Thanks
Saravana Kumar C
No comments:
Post a Comment