大家帮帮忙？UP者有分。

[lqdmafeng]

如何将一个表的数据行取出来生成一个视图，使表的数据行的记录变为视图的字段，而且表的数据是不确定的.
例如：我现在的成绩表是
学号，姓名，课程名称，成绩
01 刘勤东 语文 80
02 刘勤东 数学 75
我想转成
学号，姓名，语文，数学
01 刘勤东 80 75


请大家帮忙解决，谢谢

[nik_Amis]

up

[sdhdy]

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

[lqdmafeng]

哦，我刚开始时说错了，是成绩视图，我重新说说，大家看能不能拿试图来实现。
成绩表：
studentid courseid achieve
0001 001 80
0001 002 80
学籍表：
Studentid studentname
0001 刘勤东
课程表：
courseid coursename
001 语文
002 数学
成绩视图是根据学籍表，课程表，成绩表选择出来的
学号 姓名 课程名称 成绩
0001 刘勤东 语文 80
0002 刘勤东 数学 75

我想转成
学号，姓名，语文，数学
01 刘勤东 80 75


请大家帮忙解决，谢谢

[阿强]

up

[jycsmu]

我也想过多种办法，可用动态生成生数据窗口的办法。先对要做列的分组，得到所有的值，再用这些值生成SQL语句!

[caiyunxia]

如果课程名称 不会增加
可以不用动态SQL
select 学号,姓名，sum(case 课程名称 when 语文 then 成绩 else 0 end) 语文，
sum(case 课程名称 when 数学 then 成绩 else 0 end) 数学
from 有一表 group by 学号,姓名
否者只能用动态SQL
或在程序中用交叉报表

[rdsdh]

up

[lvcheng606717]

up

[pengdali]

declare @sql varchar(8000)
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

[lqdmafeng]

我使了大力的答案符合我的要求但是我不知道为什么开始的时候是set @SQL=,而到后面却是SELECT @SQL=呢?有没有人能帮我讲解讲解呢?还有主要用到了SQL的什么知识呢?

[abcccccc]

学习兼混分

[lqdmafeng]

快给我讲讲啊，
讲就揭贴

[zjy6631]

来了，别急！！
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',80

create 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 AAA

declare @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

[pengdali]

表a
列1
-------
a
b
c

要得到：
,a,b,c 字符串

declare @a varchar(8000)
set @a=''
select @a=@a+','+列1 from 表a

select @a

[zjy6631]

更进一步简化：

if exists(select Id from sysobjects where ID=Object_ID('成绩表'))
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',80

if 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 AAA

declare @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

[zjy6631]

关键部分：动态创建栏位，有多少门课就创建多少栏位
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 AAA

declare @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

[boy21cnthp]

up

[lqdmafeng]

Thanks

[st_2000]

up
呵呵。。。

[abillabillabill]

up