怎样把记录合并到一条记录上来显示？

[梦回童年001]

大家好
我有一个表，studentMark 学生成绩表

他的结构为：

自增ID ，学生ID， ，科目ID， 成绩分数 ，考试时间 ,考试标识ID
ID (int )，StudentID( int ), Kind(int ), Mark( float ) ,TestTime (DateTime), TestKindID(int)

1, ,20, ,1 ,90 ,2004-05-06 , 1
2, ,20, ,2 ,80 ,2004-05-06 , 1
3, ,20, ,3 ,70 ,2004-05-06 , 1

4, ,21, ,1 ,60 ,2004-05-06 , 1
5, ,21, ,2 ,70 ,2004-05-06 , 1
6, ,21, ,3 ,90 ,2004-05-06 , 1

7, ,23, ,1 ,50 ,2004-05-06 , 1
8, ,23, ,2 ,40 ,2004-05-06 , 1
9, ,23, ,3 ,20 ,2004-05-06 , 1

10, ,20, ,1 ,90 ,2004-02-26 , 2
11, ,20, ,2 ,80 ,2004-02-26 , 2
12, ,20, ,3 ,70 ,2004-02-26 , 2

13, ,21, ,1 ,60 ,2004-02-26 , 2
14, ,21, ,2 ,70 ,2004-02-26 , 2
14, ,21, ,3 ,90 ,2004-02-26 , 2

15, ,23, ,1 ,50 ,2004-02-26 , 2
16, ,23, ,2 ,40 ,2004-02-26 , 2
17, ,23, ,3 ,20 ,2004-02-26 , 2


我现在想用SQL 语句得到这样的结果，(也就是要把一次考试的所有科目成绩，和科目名称放在同一行来显示，而科目的多少是不定的，可能有N个)请问怎么做？
学生编号,科目，分数，科目，分数，科目，分数，考试标识

20 , 1 , 90 , 2 , 80 , 2 , 70 , 1

21 , 1 , 60 , 2 , 70 , 3 , 90 , 1

21 , 1 , 50 , 2 , 40 , 3 , 20 , 1


20 , 1 , 90 , 2 , 80 , 3 , 70 ,2

21 , 1 , 60 , 2 , 70 , 3 , 90 , 2

21 , 1 , 50 , 2 , 40 , 3 , 20, 2

如果科目很多话，就像下面这样一直增出来

21 , 1 , 50 , 2 , 40 , 3 ,13 , 4 ,55 , 5 ,66 ,6 ,20 ....... 2

[huobr]

用动态交叉表应可以实现，
参考一下：http://blog.csdn.net/fuxc/archive/2002/03/04/14528.aspx

[zjcxc]

--用动态sql语句
declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID from studentMark group by StudentID,TestKindID')

[zjcxc]

--测试

--测试数据
create table studentMark(ID int,StudentID int,Kind int,Mark float,TestTime DateTime,TestKindID int)
insert studentMark select 1, 20,1, 90,'2004-05-06',1
union all select 2, 20,2, 80,'2004-05-06',1
union all select 3, 20,3, 70,'2004-05-06',1

union all select 4, 21,1, 60,'2004-05-06',1
union all select 5, 21,2, 70,'2004-05-06',1
union all select 6, 21,3, 90,'2004-05-06',1

union all select 7, 23,1, 50,'2004-05-06',1
union all select 8, 23,2, 40,'2004-05-06',1
union all select 9, 23,3, 20,'2004-05-06',1

union all select 10, 20,1, 90,'2004-02-26',2
union all select 11, 20,2, 80,'2004-02-26',2
union all select 12, 20,3, 70,'2004-02-26',2

union all select 13, 21,1, 60,'2004-02-26',2
union all select 14, 21,2, 70,'2004-02-26',2
union all select 14, 21,3, 90,'2004-02-26',2

union all select 15, 23,1, 50,'2004-02-26',2
union all select 16, 23,2, 40,'2004-02-26',2
union all select 17, 23,3, 20,'2004-02-26',2
go

--查询
declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')
go

--删除测试
drop table studentMark

/*--结果
StudentID 科目 分数 科目 分数 科目 分数 TestKindID
----------- ---- -------- ------ -------- ------ -------- ----------
20 1 90.0 2 80.0 3 70.0 1
20 1 90.0 2 80.0 3 70.0 2
21 1 60.0 2 70.0 3 90.0 1
21 1 60.0 2 70.0 3 90.0 2
23 1 50.0 2 40.0 3 20.0 1
23 1 50.0 2 40.0 3 20.0 2
--*/

[xluzhong]

create table studentmark(ID int,StudentID int,Kind int,Mark float,TestTime DateTime,TestKindID int)
insert into studentmark
select 1, 20, 1,90 ,'2004-05-06' , 1 union all
select 2, 20, 2,80 ,'2004-05-06' , 1 union all
select 3, 20, 3,70 ,'2004-05-06' , 1 union all
select 4, 21, 1,60 ,'2004-05-06' , 1 union all
select 5, 21, 2,70 ,'2004-05-06' , 1 union all
select 6, 21, 3,90 ,'2004-05-06' , 1 union all
select 7, 23, 1,50 ,'2004-05-06' , 1 union all
select 8, 23, 2,40 ,'2004-05-06' , 1 union all
select 9, 23, 3,20 ,'2004-05-06' , 1 union all
select 10, 20, 1,90 ,'2004-02-26' , 2 union all
select 11, 20, 2,80 ,'2004-02-26' , 2 union all
select 12, 20, 3,70 ,'2004-02-26' , 2 union all
select 13, 21, 1,60 ,'2004-02-26' , 2 union all
select 14, 21, 2,70 ,'2004-02-26' , 2 union all
select 15, 21, 3,90 ,'2004-02-26' , 2 union all
select 16, 23, 1,50 ,'2004-02-26' , 2 union all
select 17, 23, 2,40 ,'2004-02-26' , 2 union all
select 18, 23, 3,20 ,'2004-02-26' , 2

DECLARE @SQL VARCHAR(8000)
SET @SQL='select distinct StudentID '
SELECT @SQL= @SQL+','''+cast(kind as nvarchar(10))+''' as ['+cast(kind as nvarchar(10))+'],(select mark from studentmark where kind='''+cast(kind as nvarchar(10))+''' and TestKindID=a.TestKindID and StudentID=a.StudentID) as mark'+cast(kind as nvarchar(10))
from
(select distinct kind from studentmark) b

set @sql=@sql+',TestKindID from studentmark a'
print @sql
exec(@sql)


drop table studentmark

[chenqianlong]

ding

[天地客人]

--用动态sql语句
declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID from studentMark group by StudentID,TestKindID')

[梦回童年001]

怎样把选得的结果放到一个临时表Ｔ里面去呀？

[梦回童年001]

我还要把结果放到临时表里，那怎么办呀？

[zjcxc]

declare @s nvarchar(4000)
set @s=''
select @s=@s+',科目='+quotename(Kind,'''')
+',分数=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
into ## from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')

select * from ##

[zjcxc]

declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(rtrim(Kind)+'科目')+'='+quotename(Kind,'''')
+','+quotename(rtrim(Kind+'分数')+'=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
into ## from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')

select * from ##

[crystal_table]

学习

[sindia]

up

[yaoyaomaomao]

可以给邹大哥结分了,答案完全正确的.:)

[梦回童年001]

服务器: 消息 156，级别 15，状态 1，行 6
在关键字 'from' 附近有语法错误。

[zjcxc]

--测试

--测试数据
create table studentMark(ID int,StudentID int,Kind int,Mark float,TestTime DateTime,TestKindID int)
insert studentMark select 1, 20,1, 90,'2004-05-06',1
union all select 2, 20,2, 80,'2004-05-06',1
union all select 3, 20,3, 70,'2004-05-06',1

union all select 4, 21,1, 60,'2004-05-06',1
union all select 5, 21,2, 70,'2004-05-06',1
union all select 6, 21,3, 90,'2004-05-06',1

union all select 7, 23,1, 50,'2004-05-06',1
union all select 8, 23,2, 40,'2004-05-06',1
union all select 9, 23,3, 20,'2004-05-06',1

union all select 10, 20,1, 90,'2004-02-26',2
union all select 11, 20,2, 80,'2004-02-26',2
union all select 12, 20,3, 70,'2004-02-26',2

union all select 13, 21,1, 60,'2004-02-26',2
union all select 14, 21,2, 70,'2004-02-26',2
union all select 14, 21,3, 90,'2004-02-26',2

union all select 15, 23,1, 50,'2004-02-26',2
union all select 16, 23,2, 40,'2004-02-26',2
union all select 17, 23,3, 20,'2004-02-26',2
go

--查询
declare @s nvarchar(4000)
set @s=''
select @s=@s
+','+quotename(rtrim(Kind)+'科目')+'='+quotename(Kind,'''')
+','+quotename(rtrim(Kind)+'分数')+'=max(case Kind when '+quotename(Kind,'''')
+' then Mark end)'
from studentMark
group by Kind
exec('select StudentID'+@s+',TestKindID
into ## from studentMark
group by StudentID,TestKindID
order by StudentID,TestKindID')

select * from ##

drop table ##
go

--删除测试
drop table studentMark

[zjcxc]

不至于最基本的调试都不会吧? 写少了一个)