Wednesday, October 2, 2013

how to generate ordered sequence for n number of n number of child

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