问如何将父级与同一行中的所有子级组合

EN

with cteParent ( ParentID, FName, LName, StudentID )
as (
              select 1, 'John', 'Doe',  11
    union all select 1, 'John', 'Doe',  12
    union all select 2, 'Jane', 'Doe',  11
    union all select 2, 'Jane', 'Doe',  12
    union all select 3, 'Joe',  'Dirt', 13
) ,
cteStudent ( StudentID, FName, LName )
as (
              select 11, 'Janet', 'Doe'
    union all select 12, 'Jackson', 'Doe'
    union all select 13, 'Jane', 'Dirt'
) ,
cteParentNumbered
as (
    select p.ParentID
         , p.FName
         , p.LName
         , p.StudentID
         , row_number() over (partition by p.ParentID order by p.StudentID) as StudentNum
    from cteParent p
)
select 
       pvt.ParentID
     , pvt.FName
     , pvt.LName
     , s1.StudentID as Student1Id
     , s1.FName as Student1FName
     , s2.StudentID as Student2Id
     , s2.FName as Student2FName
     , s3.StudentID as Student3Id
     , s3.FName as Student3FName
from cteParentNumbered p
    pivot ( min (StudentID) FOR StudentNum IN  ( [1], [2], [3] /*... add more if needed */ ) ) AS pvt
    left join cteStudent as s1 on s1.StudentID = pvt.[1]
    left join cteStudent as s2 on s2.StudentID = pvt.[2]
    left join cteStudent as s3 on s3.StudentID = pvt.[3]
    --... you can add more if needed