SQLServer limit results to 1 by two criteria

My company offers different courses. A course usually lasts five days and is usually taught by one teacher. But, there are exceptions where a course is split up beteween two teachers. Currently I have a SQL-statement which selects (or “limits”) the results down to the teacher for the first day of the course:

SELECT c.course_id,
       c.description,
       t.firstname AS teacher_firstname,
       t.lastname AS teacher_lastname,
       cd.day AS first_day,
       cd2.day AS last_day
FROM (SELECT *,
             ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY day) AS rn
      FROM Course_days) cd
     JOIN (SELECT *,
                  ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY day DESC) AS rn2
           FROM Course_days) cd2 ON cd.course_id = cd2.course_id
     JOIN Courses c ON cd.course_id = c.course_id
     JOIN Teachers t ON t.teacher_id = cd.teacher_id
WHERE rn = 1
  AND rn2 = 1;

Which returns something like this:

course_id	description	teacher_firstname	teacher_lastname	first_day	last_day
101	HTML/CSS/JS basics February 2021	John	Doe	2021-02-01	2021-02-05
102	C# February 2021	Jane	Doe	2021-02-01	2021-02-05
…	…	…	…	…	…

As you can see in the DB structure below the course 101 has two teachers (bold entry).

Is there a possibility to get a result like this?

course_id	description	teacher_firstname	teacher_lastname	second_teacher_firstname	second_teacher_lastname	first_day	last_day
101	HTML/CSS/JS basics February 2021	John	Doe	Mr.	X	2021-02-01	2021-02-05
102	C# February 2021	Jane	Doe	NULL	NULL	2021-02-01	2021-02-05
…	…	…	…	…	…

I have already spent hours searching for a possibile way to do that but didn’t really find anything.

DB Structure:

Courses:

course_id	description
…	…
101	HTML/CSS/JS basics February 2021
102	C# basics February 2021
103	Java basics February 2021
104	HTML/CSS/JS basics March 2021
105	C# basics March 2021
106	Java basics March 2021
…	…

Teachers:

teacher_id	firstname	Lastname
1	John	Doe
2	Jane	Doe
3	Ex	Ample
4	Mr.	X
…	…	…

Course_Days:

course_day_id	course_id (foreign key)	teacher_id (foreign key)	day	course_day_number
…	…	…	…	…
548	101	1	2021-02-01	1
549	101	1	2021-02-02	2
550	101	1	2021-02-03	3
551	101	1	2021-02-04	4
552	101	4	2021-02-05	5
553	102	2	2021-02-01	1
554	102	2	2021-02-02	2
555	102	2	2021-02-03	3
556	102	2	2021-02-04	4
557	102	2	2021-02-05	5
…	…	…	…	…

Answer

Those teachers are tricky, because you (presumably) want to preserve the original order.

The approach taken here is generalizable to more teachers and keeps the names in order. For each teacher, it determines the first day that teacher teaches the course. It then uses that to define “teacher1” and “teacher2” using conditional aggregation:

SELECT c.course_id, c.description,
       MAX(CASE WHEN teacher_seqnum = 1 THEN t.firstname END) AS teacher1_firstname,
       MAX(CASE WHEN teacher_seqnum = 1 THEN t.lastname END) AS teacher1_lastname,
       MAX(CASE WHEN teacher_seqnum = 2 THEN t.firstname END) AS teacher2_firstname,
       MAX(CASE WHEN teacher_seqnum = 2 THEN t.lastname END) AS teacher2_lastname,
       MIN(day) as first_day,
       MAX(day) as last_day
FROM (SELECT cd.*,
             dense_rank() OVER (PARTITION BY course_id, teacher_id ORDER BY min_teacher_day) as teacher_seqnum
       FROM (SELECT c.description, cd.*,
                    t.firstname, t.lastname,
                    ROW_NUMBER() OVER (PARTITION BY cd.course_id ORDER BY cd.day) AS seqnum,
                    MIN(cd.day) OVER (PARTITION BY cd.course_id, cd.teacher_id) as min_teacher_day
             FROM Course_days cd JOIN
                  Teachers t
                  ON t.teacher_id = cd.teacher_id JOIN
                  Courses c
                  ON c.course_id = cd.course_id
            ) cd
     ) cd
GROUP BY course_id, description