Pivot two columns

Question

I have data that looks likes:

stu_id	course_name	staff_name
1	Economics - 3	Kuzma, Brian
1	History	Hulings, Kreg
1	IHS IB Lit of Americ	Duncan, Amy
2	Marine Biology A	Crews, Erin
2	Pre-Calculus	Soderholm, Lodi
2	Environ & Wld Iss	Haberman, Helen

I am trying format it as such using SQL, so that each student's data is all in one row:

stu_id	course1	staff1	course2	staff2	etc ...
1	Economics - 3	Kuzma, Brian	History	Hulings, Kreg	etc ...
2	Marine Biology A	Crews, Erin	Pre-Calculus	Soderholm, Lodi	etc ...

Each student can have up to 6 courses and associated staff names all pivoted to a single row.
The basic query is:

SELECT dtbl_students.student_id stu_id,
       course_name,
       staff_name
FROM   k12intel_dw.ftbl_student_schedules
       INNER JOIN k12intel_dw.dtbl_students WITH (nolock)
               ON ftbl_student_schedules.student_key = dtbl_students.student_key
       INNER JOIN k12intel_dw.dtbl_staff WITH (nolock)
               ON ftbl_student_schedules.staff_key = dtbl_staff.staff_key
       INNER JOIN k12intel_dw.dtbl_courses WITH (nolock)
               ON ftbl_student_schedules.course_key = dtbl_courses.course_key
       INNER JOIN k12intel_dw.dtbl_schools WITH (nolock)
               ON ftbl_student_schedules.school_key = dtbl_schools.school_key
       INNER JOIN k12intel_dw.dtbl_school_dates period_date WITH (nolock)
               ON ftbl_student_schedules.school_dates_key =
                  period_date.school_dates_key
WHERE  local_school_year = '2019-2020'
       AND local_semester = 3  

I am attempting to pivot on course_name and staff_name.
I have managed to UNPIVOT as such:

WITH Courses
     AS (SELECT dtbl_students.student_id stu_id,
                course_name,
                staff_name
         FROM   K12intel_dw.FTBL_STUDENT_SCHEDULES
                INNER JOIN K12intel_dw.DTBL_STUDENTS WITH (Nolock)
                        ON FTBL_STUDENT_SCHEDULES.STUDENT_KEY =
                           DTBL_STUDENTS.STUDENT_KEY
                INNER JOIN K12intel_dw.DTBL_staff WITH (Nolock)
                        ON FTBL_STUDENT_SCHEDULES.staff_KEY =
                           DTBL_staff.staff_KEY
                INNER JOIN K12intel_dw.DTBL_COURSES WITH (Nolock)
                        ON FTBL_STUDENT_SCHEDULES.COURSE_KEY =
                           DTBL_COURSES.COURSE_KEY
                INNER JOIN K12intel_dw.DTBL_SCHOOLS WITH (Nolock)
                        ON FTBL_STUDENT_SCHEDULES.SCHOOL_KEY =
                           DTBL_SCHOOLS.SCHOOL_KEY
                INNER JOIN K12intel_dw.DTBL_SCHOOL_DATES period_date WITH (
                           Nolock)
                        ON FTBL_STUDENT_SCHEDULES.SCHOOL_DATES_KEY =
                           period_date.SCHOOL_DATES_KEY
         WHERE  local_school_year = '2019-2020'
                AND local_semester = 3)
SELECT *
FROM   Courses
       UNPIVOT ( Course
               FOR Value IN ([course_name],
                             [staff_name]) ) unpiv  

Which produces something like:

stu_id	course	value
1	IHS IB Economics - 3	course_name
1	Kuzma, Brian	staff_name
1	IHS IB History of th	course_name
1	Hulings, Kreg	staff_name

I have been experimenting with an unpivot then pivot or two pivots, using all the examples I can find on stack Overflow and elsewhere, but have had no success.

Answer 1

If you have a know or maximum number of pairs AND you want to try the PIVOT

Example

Select *
 From  (
        Select A.stu_id
              ,B.*
         From ( 
                Select *
                      ,RN = row_number() over (partition by stu_id order by course_name) 
                 from YourTable
               ) A
         Cross Apply ( values (concat('course_',RN),course_name)
                             ,(concat('staff_',RN),staff_name)
                     ) B(Item,Value)
       ) src
 Pivot (max(Value) for Item in ([Course_1],[Staff_1],[Course_2],[Staff_2],[Course_3],[Staff_3] )) pvt

Returns

Answer 2

I would suggest row_number() to enumerate the columns, then conditional aggregation:

select
    stu_id,
    max(case when rn = 1 then course_name end) course1,
    max(case when rn = 1 then staff_name end) staff1,
    max(case when rn = 2 then course_name end) course2,
    max(case when rn = 2 then staff_name end) staff2,
    ...
    max(case when rn = 6 then course_name end) course6,
    max(case when rn = 6 then staff_name end) staff6
from (
    select t.*, row_number() over(partition by stu_id order by course_name) rn
    from mytable t
) t
group by stu_id

This gives you 6 pairs of columns for each stu_id, with the corresponding course and staff names. Courses are sorted alphabetically over the columns. If a student has less than 6 courses, the final columns will be empty.

I am quite unclear on how your query and your sample data relate. This answer is based on the sample data and expected results.