SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.Students
(
    StudentID integer PRIMARY KEY,
    FirstName nvarchar(50) NOT NULL,
    LastName nvarchar(50) NOT NULL
);

CREATE TABLE dbo.Applications
(
    ID integer PRIMARY KEY,
    Student_ID integer NOT NULL REFERENCES dbo.Students,
    ProgramName nvarchar(50) NOT NULL
);
INSERT dbo.Students
    (StudentID, FirstName, LastName)
VALUES
    (65432, N'Robert', N'De Niro'),
    (65431, N'Sponge', N'Bob');

INSERT dbo.Applications
    (ID, Student_ID, ProgramName)
VALUES
    (1, 65432, N'ComputerScience'),
    (2, 65432, N'Math'),
    (3, 65432, N'Cinema'),
    (4, 65431, N'Theatre'),
    (5, 65431, N'Cinema');
7 rows affected

SELECT
    P.FirstName,
    P.LastName,
    Program1 = P.[1],
    Program2 = P.[2],
    Program3 = P.[3]
FROM 
(
    SELECT
        S.FirstName, 
        S.LastName, 
        A.ProgramName,
        rn = ROW_NUMBER() OVER (
            PARTITION BY S.StudentID
            ORDER BY A.ID)
    FROM dbo.Students AS S
    JOIN dbo.Applications AS A
        ON A.Student_ID = S.StudentID
) AS Q1
PIVOT
(
    MAX(Q1.ProgramName)
    FOR rn IN ([1], [2], [3])
) AS P;


FirstName
LastName
Program1
Program2
Program3


Sponge
Bob
Theatre
Cinema
null

Robert
De Niro
ComputerScience
Math
Cinema

FirstName	LastName	Program1	Program2	Program3
Sponge	Bob	Theatre	Cinema	null
Robert	De Niro	ComputerScience	Math	Cinema