How to combine a parent with all children in the same row

SQL Statement Question: I have two tables PARENT and STUDENTS.

PARENT Table

ParentID | FName | LName | StudentID
---------+-------+-------+----------
       1 | John  | Doe   |        11
       1 | John  | Doe   |        12
       2 | Jane  | Doe   |        11
       2 | Jane  | Doe   |        12
       3 | Joe   | Dirt  |        13

STUDENT Table

StudentID | FName   | LName
----------+---------+------
       11 | Janet   | Doe
       12 | Jackson | Doe
       13 | Jane    | Dirt

Expected Outuput

ParentID | FName | LName | StudentID1 | Student1FName | StudentID2 | Student2FName |...
---------+-------+-------+------------+---------------+------------+---------------+---
       1 | John  | Doe   |         11 | Janet         |         12 | Jackson      
       2 | Jane  | Doe   |         11 | Janet         |         12 | Jackson      
       3 | Joe   | Dirt  |         13 | Jane          |       NULL | NULL

I would like to have the output of the two tables, but sometimes the parent might have more than two students and some might only have one.

How can I achieve the expected result set while still being flexible in the number of students per parent?

Note: I don't have a definite upper limit on the number of students per parent.

Here is one of the solutions, but in this particular case we have limited number of students per parent. I wrote the query for 3 students.

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

To remove limit of students per parent you should build your sql query dynamically and run it via sp_executesql, but it has no practical use. Usually this handled by client application and/or report engine. For example it can be displayed in report as hierarchical list:

• Parent 1

• student 1

• student 2

• ...

• student N

• Parent 2

• student 1

• student 2

• ...

StackExchange Database Administrators Q#217582, answer score: 2