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.

what is the difference between row1 and row3 in your output? — p2k, Sep 25 '16 at 20:43
There is no difference there, but it is an expected pattern of the data. If i could remove those repeats that would be great, but the user is fine with that for now because they want the full join of the data. — mgmedick, Sep 25 '16 at 20:54

score 1 · Answer 1 · answered Sep 25 '16 at 19:21

1

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.

answered Sep 25 '16 at 19:21

Gordon Linoff

1,242,037
58
646
786

Its a sort of Cartesian result I need. So for instance the first line of the results I have is give me the count of every distinct student at foster that plays both guitar and keyboard with an expertise of 5 guitar and an expertise of 8 keyboard. The results would then go down the line of all expertise's of guitar at foster and cross join that will all expertise's of keyboard at foster and get a count of distinct students of both those expertise's occurring for those instruments. – mgmedick Sep 25 '16 at 19:24
So the null in row 2 of the desired results is because there is a kid at foster who plays just guitar, and not keyboard, with an expertise of 10. – mgmedick Sep 25 '16 at 19:30

score 1 · Answer 2 · edited May 23 '17 at 12:23

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

score 0 · Accepted Answer · answered Oct 05 '16 at 21:32

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]

T-SQL pivot with count on pivoted results

3 Answers3