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

Hi,

Here is another solution that is regarding performance a little better then the previous one. Here you have a table function that you call with cross apply.

if object_id('dbo.River') is not null drop table dbo.River
create table dbo.River (id bigint, rivername varchar(100), parentid bigint)

insert into dbo.River (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)

go

if object_id ('dbo.RiverHierarchy') is not null drop function dbo.RiverHierarchy
go

create function dbo.RiverHierarchy (@id bigint)
returns @rettbl table
(
        id bigint
        , parentid bigint
        , rivername varchar(100)
        , Hierarchy varchar(1000)
)
as
begin

        ;with cte
        as (
                select
                        r.id
                        , r.parentid
                        , r.rivername
                        , '.' + replicate('0', 5 - len(convert(varchar(30), r.id))) + convert(varchar(30), r.id) + '.' as Hierarchy
                from dbo.River r
                where r.id = @id

                union all

                select
                        c.id
                        , r.parentid
                        , r.rivername
                        , '.' + replicate('0', 5 - len(convert(varchar(30), r.id))) + convert(varchar(30), r.id) + c.Hierarchy
                from dbo.River r
                        inner join cte c on r.id = c.parentid
        )
        insert into @rettbl (id, parentid, rivername, Hierarchy)
        select
                c.id, c.parentid, c.rivername, c.Hierarchy
        from cte c
        where c.parentid is null

        return;
end

go

select
        r.*
        , rh.Hierarchy
from dbo.River r
        cross apply dbo.RiverHierarchy (r.id) rh
order by rh.Hierarchy

If you look at the execution plan you will see that SQL server calls this function only once but the recursion inside the function could kill your performance.

---

HTH, Regards, Dean Savović