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

Hi,

I think something like this can help you. Rivers are sorted by id columns and children are under its parent. The query is recursive.

If you don't want recursion in your query consider implementing the hierarchy notation in your table or using hierarchyid data type.

declare @tbl table (id int, rivername varchar(100), parentid int)

insert into @tbl (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)

;with River
as (
        select
                *
                , '.' + replicate ('0', 5 - len(convert(varchar(max), t.id))) + convert(varchar(max), t.id) + '.' as Hierarchy
        from @tbl t
        where t.parentid is null

        union all

        select
                tC.*
                , r.Hierarchy + replicate ('0', 5 - len(convert(varchar(max), tC.id))) + convert(varchar(max), tC.id) + '.'
        from @tbl tC
                inner join River r on tC.parentID = r.id
)
select
*
from River r
order by r.Hierarchy

---

HTH, Regards, Dean Savović