solution, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
    --Batches are separated by 'go'
    
    select @@version as 'sql server version'
    
    
    CREATE TABLE events (
        id int NOT NULL,
        event_name varchar(100) NOT NULL,
        event_id int NOT NULL
    );
    
    INSERT INTO events (id, event_name, event_id)
    VALUES
        (1, 'dance', 1),
        (2, 'sing', 2),
        (3, 'acting', 3),
        (4, 'debate', 4);
    
    
    CREATE TABLE students (
      id int NOT NULL,
      student_name text NOT NULL,
      event_id int NOT NULL
    );
    
    INSERT INTO students (id, student_name, event_id)
    VALUES
        (1, 'student1', 1),
        (2, 'student1', 2),
        (3, 'student1', 3),
        (4, 'student1', 4),
        (5, 'student2', 3),
        (6, 'student3', 2),
        (7, 'student3', 4);
        
    SELECT DISTINCT(cast(s.student_name as NVARCHAR(100))),
           stuff (g.student_name, 1, 1, '') as events
    FROM   Students s
    
    CROSS APPLY
    (
        SELECT ',' + e.event_name
        FROM  Students s1
        left join events e on s1.event_id = e.event_id 
        where cast(s1.student_name as NVARCHAR(100)) = cast(s.student_name as NVARCHAR(100))
        ORDER BY e.event_name 
        FOR XML PATH ('')
    ) g (student_name)

View schema
Execution time: 0,03 sec, rows selected: 4, rows affected: 11, absolute service time: 0,17 sec