T-SQL pivot with count on pivoted results

Question

I have the following data that I would like to pivot and get a count based on the pivoted results.

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

 INSERT INTO @tempMusicSchoolStudent(school, studentname, instrumentname, expertise)
 SELECT 'Foster','Matt','Guitar','10'
 UNION
 SELECT 'Foster','Jimmy','Guitar','5'
 UNION
 SELECT 'Foster','Jimmy','Keyboard','8'
 UNION
 SELECT 'Foster','Ryan','Keyboard','9' 
 UNION
 SELECT 'Midlothean','Kyle','Keyboard','10'
 UNION
 SELECT 'Midlothean','Mary','Guitar','4'
 UNION
 SELECT 'Midlothean','Mary','Keyboard','7'

Raw data:

I'd like the results to look like the data below....

I got this data using the sql query below. The problem with this query is that I have a dynamic amount of instruments (I've only shown 2 in this example for simplicity sake). I'd like to use pivot because it will be cleaner dynamic sql. Otherwise I would have to dynamically left join the table to itself for each instrument.

SELECT 
    t.school, t.instrumentname, t.expertise,
    t1.instrumentname, t1.expertise,
    COUNT(DISTINCT t.studentname) [DistinctStudentCount]
FROM 
    @tempMusicSchoolStudent t
LEFT JOIN 
    @tempMusicSchoolStudent t1 ON t1.school = t.school 
                               AND t1.studentname = t.studentname 
                               AND t.instrumentname <> t1.instrumentname
GROUP BY 
    t.school, t.instrumentname, t.expertise, t1.instrumentname, t1.expertise
ORDER BY 
    t.school, t.instrumentname, t.expertise, t1.instrumentname, t1.expertise

If anyone has any ideas on how I can do this in a cleaner way than dynamically left joining the table to itself it would be much appreciated. Thanks.

Answer 1

Here's the solution I was looking for, I had to use unpivot + pivot.

The real thing that I was struggling with was selecting multiple values for the column that is being pivoted, instead of the max value.

So in this case I wanted multiple "expertise" numbers under a given "instrument expertise" column. Not just the maximum expertise for that instrument.

The first key to understanding the solution is that the pivot statement is doing an implicit group by on the columns being selected. So in order to achieve multiple values under your pivoted column you have to keep the integrity of the column you are grouping on by including some type of dense_rank/rank/row_number. This basically represents changes in the value of the column you are pivoting on and is then included in the implicit group by the pivot is doing, which results in getting multiple values in the pivoted column, not just the max.

So in the code below the "expertisenum" column is keeping the integrity of the expertise data.

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

INSERT INTO @tempMusicSchoolStudent(school, studentname, instrumentname, expertise)
SELECT 'Foster','Matt','Guitar','10'
UNION
SELECT 'Foster','Jimmy','Guitar','5'
UNION
SELECT 'Foster','Jimmy','Keyboard','8'
UNION
SELECT 'Foster','Ryan','Keyboard','9' 
UNION
SELECT 'Midlothean','Kyle','Keyboard','10'
UNION
SELECT 'Midlothean','Mary','Guitar','4'
UNION
SELECT 'Midlothean','Mary','Keyboard','7'



SELECT school, [Guitar expertise], [Keyboard expertise], COUNT(*) [Count]
FROM
(
    SELECT school,[expertiseNum],
    CASE WHEN [Columns]='expertise' THEN instrumentname + ' expertise'
         END [Columns1], [Values] AS [Values1]
    FROM
    (
        SELECT school, studentname, instrumentname, DENSE_RANK() OVER(PARTITION BY school,instrumentname ORDER BY expertise) AS [expertiseNum],
        CONVERT(VARCHAR(255),expertise) AS [expertise]
        FROM @tempMusicSchoolStudent
    ) x
    UNPIVOT (
        [Values] FOR [Columns] IN ([expertise])
    ) unpvt
) p
PIVOT (
    MAX([Values1]) FOR [Columns1] IN ([Guitar expertise], [Keyboard expertise])
) pvt
GROUP BY school,[Guitar expertise], [Keyboard expertise]

Answer 2

You can try to make it dynamic for multipe instruments. Refer

;with cte
as
(
SELECT * from
(SELECT * FROM  @tempMusicSchoolStudent t) x
PIVOT
(MAX(expertise) FOR instrumentname in ([Guitar], [Keyboard])) y
)

SELECT school, studentname, 
 expertise = case when Guitar is not null then 'Guitar' else NULL end,
 Guitar AS instrumentname,
 expertise = case when Keyboard is not null then 'Keyboard' else NULL end,
 Keyboard AS instrumentname,
 count(distinct studentname) AS [DistinctStudentCount]
from cte
group by school,studentname, Guitar, Keyboard

OUTPUT:

Foster          Jimmy   Guitar  5     Keyboard  8      1
Foster          Matt    Guitar  10    NULL      NULL   1
Foster          Ryan    NULL    NULL  Keyboard  9      1
Midlothean      Kyle    NULL    NULL  Keyboard  10     1
Midlothean      Mary    Guitar  4     Keyboard  7      1

Answer 3

You just need conditional aggregation:

SELECT t.school, t.instrumentname, t.expertise, t.instrumentname, 
       COUNT(DISTINCT t.studentname) as DistinctStudentCount
FROM @tempMusicSchoolStudent t
GROUP BY t.school, t.instrumentname, t.expertise, t.instrumentname;

You have rows with NULL values. It is entirely unclear where those come from. Your question is focused on some notion of "pivoting" where it seems that you only need aggregation. But it doesn't explain where the NULL rows comes from.