Case When PK PIVOT

SELECT *
FROM ScoreInfo
go

Name Course Score
---------- ---------- -----------
Lucy Chinese 74
Jim Math 83
Angkor English 93
Tom Chinese 74
Leon Math 84
Evan English 94

(6 row(s) affected)

SELECT Name ,
MAX(CASE Course
WHEN 'Chinese' THEN Score
ELSE 0
END) Chinese ,
MAX(CASE Course
WHEN 'Math' THEN Score
ELSE 0
END) Math ,
MAX(CASE Course
WHEN 'English' THEN Score
ELSE 0
END) English
FROM ScoreInfo
GROUP BY name

Name Chinese Math English
---------- ----------- ----------- -----------
Angkor 0 0 93
Evan 0 0 94
Jim 0 83 0
Leon 0 84 0
Lucy 74 0 0
Tom 74 0 0

(6 row(s) affected)

SELECT *
FROM ScoreInfo PIVOT( MAX(Score) FOR Course IN ( Chinese, Math, English ) ) a

Name Chinese Math English
---------- ----------- ----------- -----------
Angkor NULL NULL 93
Evan NULL NULL 94
Jim NULL 83 NULL
Leon NULL 84 NULL
Lucy 74 NULL NULL
Tom 74 NULL NULL

(6 row(s) affected)

转载于:https://www.cnblogs.com/wujiakun/p/3449714.html