SQLServer将一列分成多列

中间部分查询可以做视图

SELECT PEAppM0b02,COUNT([COUNT])[COUNT],CourseName,COUNT(A)A,COUNT(B)B,COUNT(C)C,COUNT(D)D FROM (
SELECT PEAppM0b02,COUNT([COUNT])[COUNT],CourseName,
CASE PEAppM0b162 
WHEN '一等奖' THEN PEAppM0b162 END A,
CASE PEAppM0b162
WHEN '二等奖' THEN PEAppM0b162 END B,
CASE PEAppM0b162
WHEN '三等奖' THEN PEAppM0b162 END C,
CASE ISNULL(PEAppM0b162,'')
WHEN '无奖项' THEN PEAppM0b162 END D
FROM 
(
SELECT * FROM 
(SELECT PEAppM0b02,COUNT(PEAppM0b162)[COUNT],CourseName,PEAppM0b162 
FROM PEAppM002Paper where PEAppM0b162='一等奖' GROUP BY PEAppM0b02,CourseName,PEAppM0b162) A
UNION
(SELECT PEAppM0b02,COUNT(PEAppM0b162)[COUNT],CourseName,PEAppM0b162 
FROM PEAppM002Paper where PEAppM0b162='二等奖' GROUP BY PEAppM0b02,CourseName,PEAppM0b162)
UNION
(SELECT PEAppM0b02,COUNT(PEAppM0b162)[COUNT],CourseName,PEAppM0b162 
FROM PEAppM002Paper where PEAppM0b162='三等奖' GROUP BY PEAppM0b02,CourseName,PEAppM0b162)
UNION
(SELECT PEAppM0b02,COUNT(PEAppM0b162)[COUNT],CourseName,PEAppM0b162 
FROM PEAppM002Paper where (PEAppM0b162='无奖项' OR ISNULL(PEAppM0b162,'')='无奖项') GROUP BY PEAppM0b02,CourseName,PEAppM0b162)
)TB
GROUP BY PEAppM0b02,CourseName,PEAppM0b162
)AS TAB
WHERE PEAppM0b02='164'
GROUP BY PEAppM0b02,CourseName