--如果只看最后给出的这个数据,可以写出这样的处理结果create table tb(col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 int,col6 datetime) insert tb select '04-W01','1001','book1','第一单元',86,'2003.3.5' union all select '04-W01','1001','book1','第二单元',90,'2003.4.5' union all select '04-W01','1001','book1','期中' ,88,'2003.5.5' union all select '04-W01','1001','book1','期未' ,92,'2003.7.5' union all select '04-W01','1001','book2','期中' ,86,'2004.4.5' union all select '04-W01','1001','book2','期未' ,90,'2004.7.5' go--处理 declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000) select @s1='',@s2='',@s3='' select @s1=@s1+','''+col3+'''' ,@s2=@s2+','''+col4 +'''' ,@s3=@s3+',max(case when col3='''+col3+''' and col4='''+col4+''' then cast(col5 as varchar) else '''' end)' from tb group by col3,col4 order by col3,col4 exec(' select '''','''''+@s1+' union all select '''','''''+@s2+' union all select col1,col2'+@s3+' from tb group by col1,col2') go--删除测试 drop table tb/*--测试结果 book1 book1 book1 book1 book2 book2 第二单元 第一单元 期未 期中 期未 期中 04-W01 1001 90 86 92 88 90 86--*/
按邹大哥已解决问题,若只查询某班时,应该怎么改,我改为如下时(加入查询条件班级)时仅列出某册书的考试成绩 declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@a varchar(20) select @s1='',@s2='',@s3='',@a='01-W04' select @s1=@s1+','''+book+'''' ,@s2=@s2+','''+content +'''' ,@s3=@s3+',max(case when book='''+book+''' and content='''+content+''' then cast(scores as varchar) else '''' end)' from M_score where cid=@a --此处为新加入条件,不知为何不对,谢谢了 group by book,content order by book,content exec(' select '''','''''+@s1+' union all select '''','''''+@s2+' union all select cid,sid'+@s3+' from M_score group by cid,sid ')
insert tb select '04-W01','1001','book1','第一单元',86,'2003.3.5'
union all select '04-W01','1001','book1','第二单元',90,'2003.4.5'
union all select '04-W01','1001','book1','期中' ,88,'2003.5.5'
union all select '04-W01','1001','book1','期未' ,92,'2003.7.5'
union all select '04-W01','1001','book2','期中' ,86,'2004.4.5'
union all select '04-W01','1001','book2','期未' ,90,'2004.7.5'
go--处理
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
select @s1='',@s2='',@s3=''
select @s1=@s1+','''+col3+''''
,@s2=@s2+','''+col4 +''''
,@s3=@s3+',max(case when col3='''+col3+''' and col4='''+col4+''' then cast(col5 as varchar) else '''' end)'
from tb
group by col3,col4
order by col3,col4
exec('
select '''','''''+@s1+'
union all
select '''','''''+@s2+'
union all
select col1,col2'+@s3+'
from tb group by col1,col2')
go--删除测试
drop table tb/*--测试结果 book1 book1 book1 book1 book2 book2
第二单元 第一单元 期未 期中 期未 期中
04-W01 1001 90 86 92 88 90 86--*/
T1:
SID,NAME,SEX,BIRTHDAY,ADDRESS,TEL,
1001,张二,男,1986.10.5 aaaa,139888
1002,李三,男,1987.12.23,bbbb,123223
1003,王五,女,1999.12.11,cccc,123412T2:
BID,Name,Press...
101,ID-01,
102,ID-02,
103,ID-03,
T3:
CID,Name,...
C01,03-W01,
C02,03-W02,
C03,03-S01,
C04,03-S02....
T4:
CID,SID,BID,Content,Score,YMD...
C01,1001,101,第一单元,85,2003.5.10
C01,1001,101,第二单元,90,2003.5.20
C01,1001,101,期 中,70,2003.5.20
C01,1001,101,期 未,80,2003.5.20
C01,1001,102,期 中, 85,2004,3.10
C01,1001,102,期 未, 85,2004,3.10
C01,1002,101,第一单元,85,2003.5.10
C01,1002,101,期 中,70,2003.5.20
C01,1002,101,期 未,80,2003.5.20
C01,1002,102,期 中, 85,2004,3.10
C01,1002,102,期 未, 85,2004,3.10要得到查询结果:
班级,姓名,ID-01(第一单元),ID-01(第二单元),ID-01(期中),ID-01(期未),ID-02(期中),ID-02(期未).....
03-W01,张二,85,90,70,80,85,85
03-W01,李三,85,,70,80,85,85
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@a varchar(20)
select @s1='',@s2='',@s3='',@a='01-W04'
select @s1=@s1+','''+book+''''
,@s2=@s2+','''+content +''''
,@s3=@s3+',max(case when book='''+book+''' and content='''+content+''' then cast(scores as varchar) else '''' end)'
from M_score where cid=@a --此处为新加入条件,不知为何不对,谢谢了
group by book,content
order by book,content
exec('
select '''','''''+@s1+'
union all
select '''','''''+@s2+'
union all
select cid,sid'+@s3+'
from M_score group by cid,sid ')