实现这种功能的行转列的sql语句

Question

如有下列表
班级  学号  姓名  类别   分值   时间
一班   01  张三   迟到  -0.3   2012.05.01
一班   01  张三   迟到  -0.3   2012.05.02
一班   01  张三   迟到  -0.3   2012.05.03
二班   02  李四   旷课  -0.5   2012.05.04
三班   03  王五   旷课  -0.5   2012.05.04
三班   04  赵六   早退  -0.4   2012.05.05
三班   04  赵六   迟到  -0.4   2012.05.05我要得到时间从2012.05.02 到 2012.05.05 的统计：（这里的where条件不至时间一项，有多条，如班级等）
班级  学号  姓名 迟到 旷课 早退 合计
一班   01  张三  -0.6 0   0   -0.6
二班   02  李四  0   -0.5 0   -0.5
三班   03  王五  0   -0.5 0    -0.5
三班   04  赵六  -0.4 0   -0.4 -0.8这样的sql 怎样写，如果用pivot关键字又怎样写？

Answer 1

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([班级] varchar(4),[学号] varchar(2),[姓名] varchar(4),[类别] varchar(4),[分值] numeric(2,1),[时间] datetime)
insert [tb]
select '一班','01','张三','迟到',-0.3,'2012.05.01' union all
select '一班','01','张三','迟到',-0.3,'2012.05.02' union all
select '一班','01','张三','迟到',-0.3,'2012.05.03' union all
select '二班','02','李四','旷课',-0.5,'2012.05.04' union all
select '三班','03','王五','旷课',-0.5,'2012.05.04' union all
select '三班','04','赵六','早退',-0.4,'2012.05.05' union all
select '三班','04','赵六','迟到',-0.4,'2012.05.05'
godeclare @sql varchar(8000)
select @sql=isnull(@sql+',','')
  +'sum(case when 类别='''+类别+''' then 分值 else 0 end) as ['+类别+']'
from
(select distinct 类别 from tb where 时间 between '2012-05-02' and '2012-05-05') texec ('select 班级,学号,姓名,'
  +@sql
  +',sum(分值) as 合计 from tb where 时间 between ''2012-05-02'' and ''2012-05-05'' group by 班级,学号,姓名'
  )
/**
班级   学号   姓名   迟到                                      旷课                                      早退                                      合计
---- ---- ---- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
二班   02   李四   0.0                                     -0.5                                    0.0                                     -0.5
三班   03   王五   0.0                                     -0.5                                    0.0                                     -0.5
三班   04   赵六   -0.4                                    0.0                                     -0.4                                    -0.8
一班   01   张三   -0.6                                    0.0                                     0.0                                     -0.6(4 行受影响)
**/
其他条件一样加到后面即可

Answer 2

谢谢，1楼的朋友，能写成哪种没有 @sql 的写法吗，就是直接是sql 查询字符串的哪种？

Answer 3

select 班级,学号,姓名,
       迟到=sum(case when 类别='迟到' then 分值 else 0 end),
       旷课=sum(case when 类别='旷课' then 分值 else 0 end),
       早退=sum(case when 类别='早退' then 分值 else 0 end),
from tb where 时间 between '2012-05-02' and '2012-05-05'
group by 班级,学号,姓名

Answer 4

http://www.myexception.cn/sql-server/795240.html我看了半天，你也看看，有关键字pivot的使用！看看应该符合楼主的要求！

Answer 5

--动态语句
if(object_id('a')is not null) drop table a
go
create table a
(
grade varchar(50),
studentID varchar(2),
[name] varchar(50),
category varchar(50),
score decimal(18,2),
date datetime
)
goinsert into a
select '一班','01','张三','迟到',-0.3,'2012.05.01' union all
select '一班','01','张三','迟到',-0.3,'2012.05.02' union all
select '一班','01','张三','迟到',-0.3,'2012.05.03' union all
select '二班','02','李四','旷课',-0.5,'2012.05.04' union all
select '三班','03','王五','旷课',-0.5,'2012.05.04' union all
select '三班','04','赵六','早退',-0.4,'2012.05.05' union all
select '三班','04','赵六','迟到',-0.4,'2012.05.05'
go
select * from adeclare @sql varchar(7500)
set @sql = ''
select  @sql = @sql+', sum(case when [category]='+quotename([category],'''')+' then '+quotename('score')+' else 0 end) as '+quotename([category],'''') from a  group by category
exec ('select *,[迟到]+旷课+[早退] as ''合计'' from (select grade,studentID,name'+ @sql+' from a group by grade,studentID,name)as a')/*
grade                                              studentID name                                               迟到                                      旷课                                      早退                                      合计
-------------------------------------------------- --------- -------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
二班                                                 02        李四                                                 0.00                                    -0.50                                   0.00                                    -0.50
三班                                                 03        王五                                                 0.00                                    -0.50                                   0.00                                    -0.50
三班                                                 04        赵六                                                 -0.40                                   0.00                                    -0.40                                   -0.80
一班                                                 01        张三                                                 -0.90                                   0.00                                    0.00                                    -0.90(4 行受影响)
*/

Answer 6

谢谢朋友们。
3楼的朋友，如加上合计，应该是怎样写？
5楼的朋友，能写成哪种没有 @sql 的写法吗，就是直接是sql 查询字符串的哪种？ 

Answer 7

--静态语句 
if(object_id('a')is not null) drop table a 
go 
create table a ( 
grade varchar(50), 
studentID varchar(2), 
[name] varchar(50), 
category varchar(50), 
score decimal(18,2), 
date datetime ) 
go  
 insert into a 
select '一班','01','张三','迟到',-0.3,'2012.05.01' union all
select '一班','01','张三','迟到',-0.3,'2012.05.02' union all
select '一班','01','张三','迟到',-0.3,'2012.05.03' union all
select '二班','02','李四','旷课',-0.5,'2012.05.04' union all
select '三班','03','王五','旷课',-0.5,'2012.05.04' union all
select '三班','04','赵六','早退',-0.4,'2012.05.05' union all
select '三班','04','赵六','迟到',-0.4,'2012.05.05'
go 
  
select *,[迟到]+旷课+[早退] as '合计' 
from (
       select grade,studentID,name, sum(case when [category]='迟到' then [score] else 0 end) as '迟到'
                                  , sum(case when [category]='旷课' then [score] else 0 end) as '旷课'
                                  , sum(case when [category]='早退' then [score] else 0 end) as '早退' 
       from a 
       group by grade,studentID,name
)as a
/*
grade                                              studentID name                                               迟到                                      旷课                                      早退                                      合计
-------------------------------------------------- --------- -------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
二班                                                 02        李四                                                 0.00                                    -0.50                                   0.00                                    -0.50
三班                                                 03        王五                                                 0.00                                    -0.50                                   0.00                                    -0.50
三班                                                 04        赵六                                                 -0.40                                   0.00                                    -0.40                                   -0.80
一班                                                 01        张三                                                 -0.90                                   0.00                                    0.00                                    -0.90(4 行受影响)
*/

Answer 8

额，看看这个样行不行？select 班级,学号,姓名,
MAX(case 类别 when '迟到' then 分值 else 0 end) 迟到,
MAX(case 类别 when '旷课' then 分值 else 0 end) 旷课,
MAX(case 类别 when '早退' then 分值 else 0 end) 早退,
SUM(分值)总分值 from #ta where 时间  between '2012-05-02' and '2012-05-05'  group by 班级,学号,姓名
-----------------------------华丽的分隔符-------------------------------------------------------
班级   学号   姓名   迟到                                      旷课                                      早退                                      总分值
---- ---- ---- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
二班   02   李四   0.0                                     -0.5                                    0.0                                     -0.5
三班   03   王五   0.0                                     -0.5                                    0.0                                     -0.5
三班   04   赵六   0.0                                     0.0                                     0.0                                     -0.8
一班   01   张三   -0.3                                    0.0                                     0.0                                     -0.6(4 行受影响)简单明了，哇咔咔！
ps：你的进步就是我的成长！

Answer 9

sorry，我写错了，不是MAX而是SUM
select 班级,学号,姓名,
SUM(case 类别 when '迟到' then 分值 else 0 end) 迟到,
SUM(case 类别 when '旷课' then 分值 else 0 end) 旷课,
SUM(case 类别 when '早退' then 分值 else 0 end) 早退,
SUM(分值)总分值 from #ta where 时间  between '2012-05-02' and '2012-05-05'  group by 班级,学号,姓名

Answer 10

谢谢热心的朋友，我的这几项类别 如：迟到，早退，旷课 是动态的，用户有时选择一项，有时是三项，有时还有其它的项，所以后面的sum(分值) as 合计，是不行的，它统计合计的是表中的所有类别。是不是这样呢？