求SQL语句 内容如下 难度不小，牛B的来

Question

create table tb(学号 varchar(10),姓名 varchar(10),课程名称 varchar(10),成绩 int,班级编号 int,学期 int)
insert into tb values('001','李一','数学',       99  ,    101,       1) 
insert into tb values('001','李一','语文',       100 ,    101,       1) 
insert into tb values('001','李一','英语',       80  ,    101,       1) 
insert into tb values('001','李一','数学',       99  ,    101,       2) 
insert into tb values('001','李一','语文',       100 ,    101,       2) 
insert into tb values('001','李一','英语',       80  ,    101,       2)
godeclare @sql varchar(8000)
set @sql = 'select 学号,姓名,学期'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then 课程名称 else '' '' end) [课程' + cast(px as varchar) + ']'
                   + ' , max(case px when ''' + cast(px as varchar) + ''' then 成绩 else 0 end) [课程' + cast(px as varchar) + ']'
from (select distinct px from (select px=(select count(1) from tb where 学号=a.学号 and 姓名=a.姓名 and 学期 = a.学期 and 班级编号=a.班级编号 and 成绩<a.成绩)+1 , * from tb a) t) as a
set @sql = @sql + ',班级编号 from (select px=(select count(1) from tb where 学号=a.学号 and 姓名=a.姓名 and 学期 = a.学期 and 班级编号=a.班级编号 and 成绩<a.成绩)+1 , * from tb a) t group by 学号,姓名,学期,班级编号'
exec(@sql) drop table tb

Answer 1

create table tb(学号 varchar(10),姓名 varchar(10),课程名称 varchar(10),成绩 int,班级编号 int,学期 int)
insert into tb values('001','李一','数学',       99  ,    101,       1) 
insert into tb values('001','李一','语文',       100 ,    101,       1) 
insert into tb values('001','李一','英语',       80  ,    101,       1) 
insert into tb values('001','李一','数学',       99  ,    101,       2) 
insert into tb values('001','李一','语文',       100 ,    101,       2) 
insert into tb values('001','李一','英语',       80  ,    101,       2)
godeclare @sql varchar(8000)
set @sql = 'select 学号,姓名,学期'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then 课程名称 else '' '' end) [课程' + cast(px as varchar) + ']'
                   + ' , max(case px when ''' + cast(px as varchar) + ''' then 成绩 else 0 end) [成绩' + cast(px as varchar) + ']'
from (select distinct px from (select px=(select count(1) from tb where 学号=a.学号 and 姓名=a.姓名 and 学期 = a.学期 and 班级编号=a.班级编号 and 成绩<a.成绩)+1 , * from tb a) t) as a
set @sql = @sql + ',班级编号 from (select px=(select count(1) from tb where 学号=a.学号 and 姓名=a.姓名 and 学期 = a.学期 and 班级编号=a.班级编号 and 成绩<a.成绩)+1 , * from tb a) t group by 学号,姓名,学期,班级编号'
exec(@sql) drop table tb/*
学号  姓名  学期  课程1  成绩1  课程2 成绩2  课程3 成绩3 班级编号        
---- ---- ----  ----  ----  ----- ----- ----- ---- ----------- 
001  李一   1    英语   80    数学   99    语文  100  101
001  李一   2    英语   80    数学   99    语文  100  101
*/

Answer 2

DECLARE @T TABLE(XH INT,NAME VARCHAR(20),KC VARCHAR(20),CJ INT,BJBH INT,XQ INT)
INSERT INTO @T
SELECT 001   ,'李一'    ,'数学',       99,      101,       1 
UNION ALL SELECT 001,   '李一',    '语文',       100,     101,       1 
UNION ALL SELECT 001,   '李一',    '英语',       80,      101,       1 
UNION ALL SELECT 001,   '李一',    '数学',       99,      101,       2 
UNION ALL SELECT 001,   '李一',    '语文',       100,     101,       2 
UNION ALL SELECT 001,   '李一',    '英语',       80,      101,       2 SELECT XH,NAME,XQ,KC1='数学',MAX(CASE WHEN KC='数学' THEN CJ END) CJ1,
KC2='语文',MAX(CASE WHEN KC='语文' THEN CJ END) CJ2,
KC3='英语',MAX(CASE WHEN KC='英语' THEN CJ END) CJ3,BJBH FROM @T GROUP BY XQ,XH,NAME,BJBH

Answer 3

DECLARE @T TABLE(学号 varchar(10),姓名 varchar(10),课程名称 varchar(10),成绩 int,班级编号 int,学期 int)
INSERT INTO @T
SELECT 001   ,'李一'    ,'数学',       99,      101,       1 
UNION ALL SELECT 001,   '李一',    '语文',       100,     101,       1 
UNION ALL SELECT 001,   '李一',    '英语',       80,      101,       1 
UNION ALL SELECT 001,   '李一',    '数学',       99,      101,       2 
UNION ALL SELECT 001,   '李一',    '语文',       100,     101,       2 
UNION ALL SELECT 001,   '李一',    '英语',       80,      101,       2 select 学号,姓名,学期,
课程名称='数学',成绩=max(case when 课程名称='数学' then 成绩 end),
课程名称='语文',成绩=max(case when 课程名称='语文' then 成绩 end),
课程名称='英语',成绩=max(case when 课程名称='英语' then 成绩 end),班级编号 from @T group by 学号,姓名,学期,班级编号
/*
学号         姓名         学期          课程名称 成绩          课程名称 成绩          课程名称 成绩          班级编号
---------- ---------- ----------- ---- ----------- ---- ----------- ---- ----------- -----------
1          李一         1           数学   99          语文   100         英语   80          101
1          李一         2           数学   99          语文   100         英语   80          101
*/

Answer 4

create table tb(学号 varchar(10),姓名 varchar(10),课程名称 varchar(10),成绩 int,班级编号 int,学期 int)
insert into tb values('001','李一','数学',       99  ,    101,       1) 
insert into tb values('001','李一','语文',       100 ,    101,       1) 
insert into tb values('001','李一','英语',       80  ,    101,       1) 
insert into tb values('001','李一','数学',       99  ,    101,       2) 
insert into tb values('001','李一','语文',       100 ,    101,       2) 
insert into tb values('001','李一','英语',       80  ,    101,       2)
declare @str varchar(8000)
select @str='select 学号,姓名,学期 '
select @str=@str+',min(case when 课程名称='''+课程名称+''' then 成绩 end )as '''+课程名称+''''
from tb group by 课程名称
select @str=@str+' ,班级编号 from tb group by 学号,姓名,学期,班级编号'
exec (@str)学号         姓名         学期          数学          英语          语文          班级编号        
---------- ---------- ----------- ----------- ----------- ----------- ----------- 
001        李一         1           99          80          100         101
001        李一         2           99          80          100         101警告: 聚合或其它 SET 操作消除了空值。

Answer 5

试试PIVOT.set nocount on
create table scores
(studentID varchar(10), cname varchar(10), courseName varchar(20), score float,
 classID varchar(10), semester int)insert scores
select '001', 'Li Yi', 'Mathematics', 99, '101', 1
union all select '001', 'Li Yi', 'Language Arts', 100, '101', 1
union all select '001', 'Li Yi', 'English', 80, '101', 1
union all select '001', 'Li Yi', 'Mathematics', 99, '101', 2
union all select '001', 'Li Yi', 'Language Arts', 100, '101', 2
union all select '001', 'Li Yi', 'English', 80, '101', 2
select studentID, cname, semester, 'Mathematics' as [Course1],
Mathematics as Score1, 'Language Arts' as [Course2],
[Language Arts] as Score2, 'English' as [Course3], 
English as Score3, classID from scores
pivot
( 
max(score)
for courseName
in (Mathematics, [Language Arts], English)
) as p
drop table scoresstudentID  cname      semester    Course1     Score1                 Course2       Score2                 Course3 Score3                 classID
---------- ---------- ----------- ----------- ---------------------- ------------- ---------------------- ------- ---------------------- ----------
001        Li Yi      1           Mathematics 99                     Language Arts 100                    English 80                     101
001        Li Yi      2           Mathematics 99                     Language Arts 100                    English 80                     101

Answer 6

sql2005可以试试declare @s varchar(1000)
select @s=isnull(@s,'') + ',' +  课程名称 from tb group by 课程名称
select @s='select * from tb pivot(sum(成绩) for 课程名称 in(' + stuff(@s,1,1,'') + ')) pvt'
exec(@s)