Transpose group per sequence, Sql Server

Language: Layout:

IF OBJECT_ID ('tempdb..#t') IS NOT NULL DROP TABLE #t
IF OBJECT_ID ('tempdb..#m') IS NOT NULL DROP TABLE #m
  
CREATE TABLE #t(FullName NVARCHAR(50),ID NVARCHAR(22),FamilyMember INT,Related NVARCHAR(20))
  
INSERT INTO #t VALUES
    ('FSDFE','23123312',1,'Household'),
    ('BVCB','34652',2,'Household'),
    ('JHGTYJ','765',2,'Spouse'),
    ('ZC','1236',3,'Household'),
    ('ER','0898',3,'Spouse'),
    ('VBCV','7567',3,'Daughter'),
    ('OUI','09878',1,'Household'),
    ('MJGG','56456',5,'Household'),
    ('ZDF','87687',5,'Spouse'),
    ('GFDR','75647',5,'Mother'),
    ('JKTY','908768',5,'Daughter'),
    ('OUI','24',5,'Son')
     
;with a as (
      
 select *,row_number()over(partition by Related order by seq) as fid
 from (
    select *,row_number()over(order by getdate()) as seq  from #t
 ) as t
)
select a.FullName,a.seq,a.FamilyMember,a.Related,a.id,m.fid,row_number()over(partition by m.fid order by a.seq) as mid into #m from a 
cross apply(select top 1 fid from a as ma where ma.Related='Household' and ma.seq<=a.seq order by id desc) m
 
 --order by id
 declare @sql nvarchar(max)
 select @sql=isnull(@sql+',','') +N'max(case when mid='+ltrim(sv.number)+N' then FullName else '''' end) as FullName'+ltrim(sv.number)
       +N',max(case when mid='+ltrim(sv.number)+N' then seq else '''' end) as seq'+ltrim(sv.number)
       +N',max(case when mid='+ltrim(sv.number)+N' then Related else '''' end) as Related'+ltrim(sv.number)
 from master.dbo.spt_values as sv 
 where sv.type='P' and sv.number between 1 and (select max(FamilyMember) from #m)
 set @sql=N'select fid,FamilyMember,'+@sql+N' from #m as m group by fid,FamilyMember order by fid'
 exec(@sql)

View schema
Execution time: 0,03 sec, rows selected: 11, rows affected: 12, absolute service time: 0,19 sec