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

Hi,

Although Mr. Joe Celko is as always right regarding mistakes in the data modeling approach I could not resist to help in this case because I don't get to write recursive functions that much.

Be careful, this solution could have very poor performance on very large data set.

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, @Hierarchy varchar(1000))
returns varchar(1000)
as
begin

        declare @parentid bigint

        if @id is not null
        begin
                set @parentid = (select r.parentid from dbo.River r where r.id = @id)
                set @Hierarchy = dbo.RiverHierarchy (@parentid, '.' + replicate('0', 5 - len(convert(varchar(30), @id))) + convert(varchar(30), @id) + '.') + @Hierarchy
        end

        return @Hierarchy
end

go

select
        *, dbo.RiverHierarchy (r.id, '') as Hierarchy
from dbo.River r
order by Hierarchy

---

HTH, Regards, Dean Savović