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:

[![enter image description here](https://i.stack.imgur.com/o34Q2.png)](https://i.stack.imgur.com/o34Q2.png)

I'd like the results to look like this:

[![enter image description here](https://i.stack.imgur.com/bJA2p.png)](https://i.stack.imgur.com/bJA2p.png)