T-SQL multiple pivot statements without cartesian result

Question

I have the T-SQL statement below where I am trying to pivot on 2 different data elements, studentname and instrumentname. The results should have only 1 line per school and the students should be pivoted across the top along with their instruments.

Unfortunately I'm getting a Cartesian result of sorts where the students and the instruments are spanning across multiple rows (see screenshot after query). How can I go about just getting one row per school like I wanted?

Query:

DECLARE @tempMusicSchoolStudent TABLE
(school VARCHAR(50),
studentname VARCHAR(50),
instrumentname VARCHAR(255))

INSERT INTO @tempMusicSchoolStudent(school, studentname, instrumentname)
SELECT 'Foster','Matt','Guitar'
UNION
SELECT 'Foster','Matt','Violin'
UNION
SELECT 'Foster','Jenny','Keyboard'
UNION
SELECT 'Midlothean','Kyle','Drums'
UNION
SELECT 'Midlothean','Mary','Piano'
UNION
SELECT 'Midlothean','Mary','Trumpet'

SELECT 
    p1.school, [Student1], [Instrument1], [Instrument2], [Student2],      [Instrument1], [Instrument2]
FROM 
    (SELECT 
         school, studentname, instrumentname,
         'Student' + CONVERT(VARCHAR(255), 
         DENSE_RANK() OVER(PARTITION BY school ORDER BY studentname)) [StudentNum],
         'Instrument' + CONVERT(VARCHAR(255),ROW_NUMBER() OVER(PARTITION BY school, studentname ORDER BY instrumentname)) AS [InstrumentNum]
     FROM 
         @tempMusicSchoolStudent) src
PIVOT
    (MAX(studentname) FOR [StudentNum] IN ([Student1], [Student2])) p
PIVOT
(
   MAX([instrumentname])
   FOR [InstrumentNum] IN ([Instrument1],[Instrument2])
) p1
ORDER BY p1.school

Bad Result Screenshot:

I'd like the results to look like this:

Answer 1

UNPIVOT + PIVOT + two DENSE_RANKs:

SELECT *
FROM (
    SELECT  school,
            CASE WHEN [Columns] = 'student' THEN [Columns] + StSeq 
                WHEN [Columns] = 'instrument' THEN [Columns] + StSeq + InSeq
                ELSE NULL END as [Columns],
            [Values]
    FROM (
        SELECT  school,
                CAST(studentname as varchar(255)) student,
                instrumentname as instrument,
                CAST(DENSE_RANK() OVER (PARTITION BY school ORDER BY studentname) as nvarchar(2)) as StSeq,
                CAST(DENSE_RANK() OVER (PARTITION BY school,studentname ORDER BY instrumentname) as nvarchar(2)) as InSeq
        FROM @tempMusicSchoolStudent
    ) t
    UNPIVOT (
        [Values] FOR [Columns] IN (student,instrument)
    ) unpvt
) p
PIVOT (
    MAX([Values]) FOR [Columns] IN (student1,instrument11,instrument12,student2,instrument21,instrument22)
) pvt

Output:

school      student1    instrument11    instrument12    student2    instrument21    instrument22
Foster      Jenny       Keyboard        NULL            Matt        Guitar          Violin
Midlothean  Kyle        Drums           NULL            Mary        Piano           Trumpet

The main idea is to get 2 sequences for students and instruments. Than UNPIVOT the data and concatenate students with there sequence numbers and instruments with both sequences, because if we use only instrument sequence - there will be no link between which student owns this instrument or whose this first (or second) instrument is.

Answer 2

This works, but is a little messy, especially to dynamically create, I was hoping for a cleaner option. I can't even think of how to do this with case statements, not sure if that would be much cleaner or not. Correct answer is still up for grabs if someone has a cleaner more readable solution. Thanks.

DECLARE @tempMusicSchoolStudent TABLE
(school VARCHAR(50),
studentname VARCHAR(50),
instrumentname VARCHAR(255))

INSERT INTO @tempMusicSchoolStudent(school,studentname, instrumentname)
SELECT 'Foster','Matt','Guitar'
UNION
SELECT 'Foster','Matt','Violin'
UNION
SELECT 'Foster','Jenny','Keyboard'
UNION
SELECT 'Midlothean','Kyle','Drums'
UNION
SELECT 'Midlothean','Mary','Piano'
UNION
SELECT 'Midlothean','Mary','Trumpet'



;WITH studentPivot AS
(
    SELECT p1.school,[Student1],[Student2]
    FROM 
    (
        SELECT school, studentname,
        'Student' + CONVERT(VARCHAR(255),DENSE_RANK() OVER(PARTITION BY school ORDER BY studentname)) [StudentNum]
        FROM @tempMusicSchoolStudent
    ) src
    PIVOT
    (
        MAX(studentname)
        FOR [StudentNum] IN ([Student1],[Student2])
    ) p1
),
instrumentPivot AS
(
    SELECT p1.studentname,[Instrument1],[Instrument2]
    FROM 
    (
        SELECT school, studentname, instrumentname,
        'Instrument' + CONVERT(VARCHAR(255),ROW_NUMBER() OVER(PARTITION BY school,studentname ORDER BY instrumentname)) AS [InstrumentNum]
        FROM @tempMusicSchoolStudent
    ) src
    PIVOT
    (
        MAX(instrumentname)
        FOR [InstrumentNum] IN ([Instrument1],[Instrument2])
    ) p1
 )

SELECT sp.school,sp.Student1,ip.Instrument1,ip.Instrument2,sp.Student2,ip1.Instrument1,ip1.Instrument2
FROM studentPivot sp
JOIN instrumentPivot ip ON ip.studentname=sp.Student1
JOIN instrumentPivot ip1 ON ip1.studentname=sp.Student2

Results: