declare @sql varchar(8000)
set @sql = 'select 学号,姓名'
select @sql = @sql + ',sum(case 课程名称 when '''+课程名称+''' then 成绩 else 0 end) ['+课程名称+']'
from (select distinct 课程名称 from 有一表) as a
select @sql = @sql+' from 有一表 group by 学号,姓名'exec(@sql)
go
set @sql = 'select 学号,姓名'
select @sql = @sql + ',sum(case 课程名称 when '''+课程名称+''' then 成绩 else 0 end) ['+课程名称+']'
from (select distinct 课程名称 from 有一表) as a
select @sql = @sql+' from 有一表 group by 学号,姓名'exec(@sql)
go
set @sql = 'select 学号,姓名'
select @sql = @sql + ',sum(case 课程名称 when '''+课程名称+''' then 成绩 else 0 end) ['+课程名称+']'
from (select distinct 课程名称 from 有一表) as a
select @sql = @sql+' from 有一表 group by 学号,姓名'exec(@sql)
go
set @sql = 'select 学号,姓名'
select @sql = @sql + ',sum(case 课程名称 when '''+课程名称+''' then 成绩 else 0 end) ['+课程名称+']'
from (select distinct 课程名称 from 有一表) as a
select @sql = @sql+' from 有一表 group by 学号,姓名'exec(@sql)
go
成绩表:
studentid courseid achieve
0001 001 80
0001 002 80
学籍表:
Studentid studentname
0001 刘勤东
课程表:
courseid coursename
001 语文
002 数学
成绩视图是根据学籍表,课程表,成绩表选择出来的
学号 姓名 课程名称 成绩
0001 刘勤东 语文 80
0002 刘勤东 数学 75我想转成
学号,姓名,语文,数学
01 刘勤东 80 75
请大家帮忙解决,谢谢
可以不用动态SQL
select 学号,姓名,sum(case 课程名称 when 语文 then 成绩 else 0 end) 语文,
sum(case 课程名称 when 数学 then 成绩 else 0 end) 数学
from 有一表 group by 学号,姓名
否者只能用动态SQL
或在程序中用交叉报表
set @sql = ''
select @sql = @sql + ',sum(case courseid when '''+courseid+''' then achieve else 0 end) ['+coursename+']'
from (select * from 课程表) as a
exec('select studentid 学号,(select studentname from 学籍表 where Studentid=成绩表.studentid) 姓名'+@sql+' from 成绩表 group by studentid')
go
create table 成绩表(
studentid varchar(10) not null,
courseid varchar(5) not null,
achieve numeric(28,8) null)select * from 成绩表
delete from 成绩表
insert 成绩表
select
'0001' , '001' , 80
union
select
'0001','002',80create table 学籍表
(
Studentid Varchar(10) not null,
studentname varchar(20) null)select * from 学籍表
insert 学籍表
select '0001' , '刘勤东'create table 课程表(
courseid Varchar(5) not null,
coursename varchar(20) null)
insert 课程表
select
'001' , '语文'
union
select
'002', '数学'
if exists(select Id from sysobjects where ID=Object_ID('AAA'))
drop table AAA
go
create table AAA(StudentID Varchar(10),
Studentname varchar(20)
primary key(studentID) )
go
declare @Coursename Varchar(20)
declare BB_Cursor cursor for
select coursename from 课程表
open BB_Cursor
fetch next from BB_Cursor into @Coursename
while @@fetch_status <>-1
begin
exec(' alter table AAA
Add '+@Coursename+' numeric(28,8) null ')
fetch next from BB_Cursor into @Coursename
end
close BB_cursor
deallocate BB_cursor go
--select * from AAA
delete from AAAdeclare @Coursename Varchar(20),
@studentID Varchar(10),
@StudentName varchar(20),
@achieve Numeric(28,8),
@OldStudentName varchar(20),
@Count Varchar(10)
declare BB_Cursor cursor for
select A.studentID,B.Studentname,C.Coursename,A.achieve
from 成绩表 A,学籍表 B,课程表 C
where A.studentID=b.Studentid and A.courseID=C.CourseID select @OldStudentName='#$^$%^'
open BB_Cursor
fetch next from BB_Cursor into @studentID,@studentName,@Coursename,@achieve
while (@@fetch_status <>-1 )
begin
if @OldStudentname<>@StudentName
begin
select @Count=Cast(@achieve As Varchar(100))
exec('Insert AAA(StudentID, StudentName,'+@Coursename+' ) select ''' +@studentID+''', '''+@studentname+''','+@Count+' ')
select @OldStudentname= @StudentName
end
else
begin
exec(' Update AAA set '+@courseName+'='+@Count+' where (StudentID='''+@StudentID+''') ')
select 1
end
fetch next from BB_Cursor into @studentID,@studentName,@Coursename,@achieve
end
close BB_cursor
deallocate BB_cursor
go
--------------------select * from AAA
go
列1
-------
a
b
c要得到:
,a,b,c 字符串declare @a varchar(8000)
set @a=''
select @a=@a+','+列1 from 表aselect @a
drop table 成绩表create table 成绩表(
studentid varchar(10) not null,
courseid varchar(5) not null,
achieve numeric(28,8) null)
insert 成绩表
select
'0001' , '001' , 80
union
select
'0001','002',80if exists(select Id from sysobjects where ID=Object_ID('学籍表'))
drop table 学籍表
create table 学籍表
(
Studentid Varchar(10) not null,
studentname varchar(20) null)insert 学籍表
select '0001' , '刘勤东'if exists(select Id from sysobjects where ID=Object_ID('课程表'))
drop table 课程表
create table 课程表(
courseid Varchar(5) not null,
coursename varchar(20) null)insert 课程表
select
'001' , '语文'
union
select
'002', '数学'
if exists(select Id from sysobjects where ID=Object_ID('AAA'))
drop table AAA
go
create table AAA(StudentID Varchar(10),
Studentname varchar(20)
primary key(studentID) )
go
declare @Coursename Varchar(20)
declare BB_Cursor cursor for
select coursename from 课程表
open BB_Cursor
fetch next from BB_Cursor into @Coursename
while @@fetch_status <>-1
begin
exec(' alter table AAA Add '+@Coursename+' numeric(28,8) null ')
fetch next from BB_Cursor into @Coursename
end
close BB_cursor
deallocate BB_cursor go
--select * from AAA
delete from AAAdeclare @Coursename Varchar(20),
@studentID Varchar(10),
@StudentName varchar(20),
@achieve Numeric(28,8),
@OldStudentName varchar(20),
@Count Varchar(10)
declare BB_Cursor cursor for
select A.studentID,B.Studentname,C.Coursename,A.achieve
from 成绩表 A,学籍表 B,课程表 C
where A.studentID=b.Studentid and A.courseID=C.CourseID select @OldStudentName='#$^$%^'
open BB_Cursor
fetch next from BB_Cursor into @studentID,@studentName,@Coursename,@achieve
while (@@fetch_status <>-1 )
begin
if @OldStudentname<>@StudentName
begin
select @Count=Cast(@achieve As Varchar(100))
exec('Insert AAA(StudentID, StudentName,'+@Coursename+' ) select ''' +@studentID+''', '''+@studentname+''','+@Count+' ')
select @OldStudentname= @StudentName
end
else
begin
exec(' Update AAA set '+@courseName+'='+@Count+' where (StudentID='''+@StudentID+''') ')
end
fetch next from BB_Cursor into @studentID,@studentName,@Coursename,@achieve
end
close BB_cursor
deallocate BB_cursor
go
select * from AAA
go
if exists(select Id from sysobjects where ID=Object_ID('AAA'))
drop table AAA
go
create table AAA(StudentID Varchar(10),
Studentname varchar(20)
primary key(studentID) )
go
declare @Coursename Varchar(20)
declare BB_Cursor cursor for
select coursename from 课程表
open BB_Cursor
fetch next from BB_Cursor into @Coursename
while @@fetch_status <>-1
begin
exec(' alter table AAA Add '+@Coursename+' numeric(28,8) null ')
fetch next from BB_Cursor into @Coursename
end
close BB_cursor
deallocate BB_cursor go
--select * from AAA
delete from AAAdeclare @Coursename Varchar(20),
@studentID Varchar(10),
@StudentName varchar(20),
@achieve Numeric(28,8),
@OldStudentName varchar(20),
@Count Varchar(10)
declare BB_Cursor cursor for
select A.studentID,B.Studentname,C.Coursename,A.achieve
from 成绩表 A,学籍表 B,课程表 C
where A.studentID=b.Studentid and A.courseID=C.CourseID select @OldStudentName='#$^$%^'
open BB_Cursor
fetch next from BB_Cursor into @studentID,@studentName,@Coursename,@achieve
while (@@fetch_status <>-1 )
begin
if @OldStudentname<>@StudentName
begin
select @Count=Cast(@achieve As Varchar(100))
exec('Insert AAA(StudentID, StudentName,'+@Coursename+' ) select ''' +@studentID+''', '''+@studentname+''','+@Count+' ')
select @OldStudentname= @StudentName
end
else
begin
exec(' Update AAA set '+@courseName+'='+@Count+' where (StudentID='''+@StudentID+''') ')
end
fetch next from BB_Cursor into @studentID,@studentName,@Coursename,@achieve
end
close BB_cursor
deallocate BB_cursor
go
select * from AAA
go