create table source(stu varchar(10),course varchar(15), int)
insert into source select 'Joe','English',98
insert into source select 'Joe','history',78
insert into source select 'Joe','jisuanji',88
insert into source select 'Ann','English',69
insert into source select 'Ann','history',77
insert into source select 'Ann','jisuanji',59
go
select a.stu,b. as English,c. as history,d. as jisuanji from
(select distinct stu from source)a left join
(select stu, from source where course='English') b on a.stu=b.stu left join
(select stu, from source where course='history') c on a.stu=c.stu left join
(select stu, from source where course='jisuanji') d on a.stu=d.stugo
drop table source
/*
stu English history jisuanji
---------- ----------- ----------- -----------
Ann 69 77 59
Joe 98 78 88(2 行受影响)
*/
insert into source select 'Joe','English',98
insert into source select 'Joe','history',78
insert into source select 'Joe','jisuanji',88
insert into source select 'Ann','English',69
insert into source select 'Ann','history',77
insert into source select 'Ann','jisuanji',59
go
select a.stu,b. as English,c. as history,d. as jisuanji from
(select distinct stu from source)a left join
(select stu, from source where course='English') b on a.stu=b.stu left join
(select stu, from source where course='history') c on a.stu=c.stu left join
(select stu, from source where course='jisuanji') d on a.stu=d.stugo
drop table source
/*
stu English history jisuanji
---------- ----------- ----------- -----------
Ann 69 77 59
Joe 98 78 88(2 行受影响)
*/
解决方案 »
- 关于sql分页的简单问题
- 如何在SQL Server 2005 Express 上安装Adventureworks
- 计算列值不为0的 其他列共有多少个?SQL查询
- sql连接端口一般都有哪些
- SQL语句如何求不同值的个数
- sql sever 2000和2005问题
- sql server死锁,求解
- 怎么样将.VSD文件存入SQL Server数据库
- 请问MSSQ的DECIMAL和NUMERIC数据类型有什么区别?分别有什么用途?
- 如何让servlet连接SQL Server 2000?(在线等待)
- 什么是数据库逻辑结构图?如何更具相关文档做出来?求救啊!
- 100分题:如何对多条字符串记录进行"汇总"运算?
declare @str varchar(500)
declare @t table(course varchar(30))
insert into @t
select distinct course
from source
set @str =''
select @str=@str+','+ 'max(case course when '+''''+course+''''
+' then end) as ' +''''+course +''''
from @t
print @strset @str='select stu'+@str+' from source group by stu'
print @str
exec(@str)
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
Set Nocount On
if not object_id('source') is null
drop table source
Go
Create table source([stu] nvarchar(3),[course] nvarchar(8),[] int)
Insert source
select N'Joe',N'English',98 union all
select N'Joe',N'history',78 union all
select N'Joe',N'jisuanji',88 union all
select N'Ann',N'English',69 union all
select N'Ann',N'history',77 union all
select N'Ann',N'jisuanji',59
GoDeclare @sql nvarchar(4000)
Select @sql=Isnull(@sql+',','Select * From source Pivot(Max(Mark) For course In(')+Quotename(course)
From source
Group By course
Exec (@sql+')) p')/*
stu English history jisuanji
---- ----------- ----------- -----------
Ann 69 77 59
Joe 98 78 88
*/