在SQL原表是这样的
student_name lesson_name result
kern maths 100
bob p.e 90
bob maths 80
kern p.e 87
现在想在网页上显示如下的表
student_name Mathes p.e
kern 100 87
bob 80 90
如何用.net实现
谢谢各位了
student_name lesson_name result
kern maths 100
bob p.e 90
bob maths 80
kern p.e 87
现在想在网页上显示如下的表
student_name Mathes p.e
kern 100 87
bob 80 90
如何用.net实现
谢谢各位了
在sql server版块中有很多这样的问题
搜索一下就可以了
(
student_name varchar(10),
lesson_name varchar(10),
result int
)
insert @tb
select 'kern','maths',100 union
select 'bob','p.e',90 union
select 'bob','maths',80 union
select 'kern','p.e',87--查询
select student_name
,max(case when lesson_name='maths' then result else 0 end) as 'maths'
,max(case when lesson_name='p.e' then result else 0 end) as 'p.e'
from @tb
group by student_name--结果
/*student_name maths p.e
------------ ----------- -----------
bob 80 90
kern 100 87(所影响的行数为 2 行)
*/
(
student_name varchar(10),
lesson_name varchar(10),
result int
)
insert A
select 'kern','maths',100 union
select 'bob','p.e',90 union
select 'bob','maths',80 union
select 'kern','p.e',87
go--查询
declare @sql varchar(8000)
set @sql='select student_name'
select @sql=@sql+',max(case when lesson_name='''+lesson_name+''' then result else 0 end) as '''+lesson_name+''''
from A group by lesson_name
select @sql=@sql+' from A group by student_name'
exec(@sql)--删除测试环境
drop table A--结果
/*student_name maths p.e
------------ ----------- -----------
bob 80 90
kern 100 87(所影响的行数为 2 行)
*/
set @sql='select student_name'
select @sql=@sql+',max(case when lesson_name='''+lesson_name+''' then result else 0 end) as '''+lesson_name+''''
from A group by lesson_name
select @sql=@sql+' from A group by student_name'
exec(@sql)
这个执行不了
TO kangl():你可以把它放到查询分析器中执行试一下
注解一下可以吗?看不太懂?