GROUP BY 子句
指定用来放置输出行的组，并且如果 SELECT 子句 中包含聚合函数，则计算每组的汇总值。指定 GROUP BY 时，选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。
即select选择的列要么在聚合函数中，要么在Group by 中进行分组。
如果你想按下面的方式显示数据
而你的数据库中实际的存储方式是如下的。即是以行的形式存储的数据，现在要变成上面的列来存储
SQL可以如下：
select s.ClassProCode,s.YearMonth,
max(case s.SurveyCode when 'SC001' then s.Score else 0 end) as '报名报道',
max(case s.SurveyCode when 'SC002' then s.Score else 0 end) as '后勤接待',
max(case s.SurveyCode when 'SC003' then s.Score else 0 end) as '副班主任'
from SerSurvey s
group by s.ClassProCode,s.YearMonth
--备注：按照SurveyCode这个条件来转换数据，如果条件成立，则转换Score这一行的数据成列显示，否则为0
或者
select A.*,
(select top 1 Score from SerSurvey
where ClassProCode = A.ClassProCode and YearMonth = A.YearMonth and SurveyCode = 'SC001') as '报名报道',
(select top 1 Score from SerSurvey
where ClassProCode = A.ClassProCode and YearMonth = A.YearMonth and SurveyCode = 'SC002') as '后勤接待',
(select top 1 Score from SerSurvey
where ClassProCode = A.ClassProCode and YearMonth = A.YearMonth and SurveyCode = 'SC003') as '副班主任'
from
(
select s.ClassProCode,s.YearMonth
from SerSurvey s
group by s.ClassProCode,s.YearMonth
) A
可以分页的：
begin WITH list AS ( SELECT ROW_NUMBER() OVER (ORDER BY s.YearMonth DESC)AS Row,
s.ClassProCode,s.YearMonth,
max(case s.SurveyCode when 'SC001' then s.Score else 0 end) as 'ApplyReport',
max(case s.SurveyCode when 'SC002' then s.Score else 0 end) as 'LogisticsReception',
max(case s.SurveyCode when 'SC003' then s.Score else 0 end) as 'ViceHeadTeacher',
max(case s.SurveyCode when 'SC001' then s.flag else 0 end) as 'ApplyReportFlag',
max(case s.SurveyCode when 'SC002' then s.flag else 0 end) as 'LogisticsReceptionFlag',
max(case s.SurveyCode when 'SC003' then s.flag else 0 end) as 'ViceHeadTeacherFlag'
from SerSurvey s
where 1=1 --And s.ClassProCode like '%ss%' And s.YearMonth='2010-05'
group by s.ClassProCode,s.YearMonth)
SELECT * FROM list WHERE Row between 1 and 10
end
参考资料的链接：