create html in sql server

create html in sql server.sql

declare @pc table(CHILD_ID int, PARENT_ID int, [NAME] varchar(80));
 
insert into @pc
select 1,NULL,'Bill' union all
select 2,1,'Jane' union all
select 3,1,'Steve' union all
select 4,2,'Ben' union all
select 5,3,'Andrew' union all
select 6,NULL,'Tom' union all
select 7,8,'Dick' union all
select 8,6,'Harry' union all
select 9,3,'Stu' union all
select 10,7,'Joe';

; with r as (
      select CHILD_ID, PARENT_ID, [NAME], depth=0, sort=cast(CHILD_ID as varchar(max))
      from @pc
      where PARENT_ID is null
      union all
      select pc.CHILD_ID, pc.PARENT_ID, pc.[NAME], depth=r.depth+1, sort=r.sort+cast(pc.CHILD_ID as varchar(30))
      from r
      inner join @pc pc on r.CHILD_ID=pc.PARENT_ID
)
select replicate('-',(r.depth)*3)+r.[NAME]  from r for xml path('li')
--select child_id,
--	   
-- from r