【SQL】——行列转换
来源:互联网 发布:最好的短线炒股软件 编辑:程序博客网 时间:2024/06/05 07:23
--Create by Ranen--bolg:http://blog.csdn.net/ranen2010
--原文链接:http://blog.csdn.net/Ranen2010/archive/2011/04/22/6341056.aspx
/*
源成绩表结构
StuName cid score
-------------------- ----------- -----------
Ranen 1 80
Ranen 2 84
Ranen 3 98
kevin 1 74
kevin 2 98
kevin 3 100
jacky 1 50
jacky 3 96
源课程表结构
ID cName
----------- ------------------------------
1 C#
2 javascript
3 Sql Server
转换后表结构
StuName C# javascript Sql Server
-------------------- ----------- ----------- -----------
jacky 50 NULL 96
kevin 74 98 100
Ranen 80 84 98
*/
Create Table CourseTable
(
ID int identity primary key,
cName nvarchar(30) not null
)
go
insert into CourseTable values('C#')
insert into CourseTable values('javascript')
insert into CourseTable values('Sql Server')
create table ScoreTable
(
StuName varchar(20) not null,--学生
cid int,--课程编号
score int --成绩
)
go
insert into ScoreTable values('Ranen',1,80)
insert into ScoreTable values('Ranen',2,84)
insert into ScoreTable values('Ranen',3,98)
insert into ScoreTable values('kevin',1,74)
insert into ScoreTable values('kevin',2,98)
insert into ScoreTable values('kevin',3,100)
insert into ScoreTable values('jacky',1,50)
insert into ScoreTable values('jacky',3,96)
go
--2000 静态行列转
select StuName,
max(case cname when 'C#' then Score else 0 end) C#,
max(case cname when 'javascript' then Score else 0 end) javascript,
max(case cname when 'Sql Server' then Score else 0 end) SqlServer
from (
select s.StuName,c.cName,s.Score from ScoreTable s
inner join CourseTable c on s.cid=c.id) T group by StuName
--2000 动态SQL
--insert into CourseTable values('html') --添加一门课程测试动态SQL
declare @sql varchar(500)
set @sql = 'select StuName'
select @sql = @sql + ' , max(case cname when '''+cName+''' then Score else 0 end) ['+cName+']'
from (select distinct cName from CourseTable) as c
set @sql = @sql + ' from (select s.StuName,c.cName,s.Score from ScoreTable s
inner join CourseTable c on s.cid=c.id) T group by StuName'
exec(@sql)
go
--2005 静态SQL
select * from (
select s.StuName,c.cName,s.Score from ScoreTable s
inner join CourseTable c on s.cid=c.id
) a pivot (max(Score) for cName in (C#,javascript,[Sql Server])) b
--2005 动态SQL
declare @sql varchar(500)
select @sql = isnull(@sql + ',' , '') + '['+cname+']' from CourseTable group by cName
exec ('select * from (
select s.StuName,c.cName,s.Score from ScoreTable s
inner join CourseTable c on s.cid=c.id
) a pivot (max(Score) for cName in (' + @sql + ')) b')
go
drop table CourseTable
drop table ScoreTable
- 【SQL】——行列转换
- 【SQL】——行列转换
- 【SQL】——行列转换
- 【SQL】——行列转换
- 【SQL基础用法】——行列转换
- 【SQL基础用法】——行列转换
- SQL经典面试题—行列转换
- SQL进行行列转换
- SQL的行列转换
- sql 行列转换
- SQL 行列转换查询
- SQL 行列转换查询
- SQL行列转换实战
- SQL 行列转换
- SQL行列转换实战
- sql server 行列转换
- SQL行列转换
- sql server 行列转换
- Java运用心得(二)
- 锻炼思维
- 图片上传
- 咏码畜(邀月于辛卯年四月十六日)
- 多线程网址
- 【SQL】——行列转换
- ASCII码表(自己保存下,便于查阅)
- android post get方法实现
- linux kernel compile update etc
- div居中的问题
- 红黑树
- C#入门经典---C#简介
- 安装好eVC4后,打开eVC时提示:Microsoft eMbedded Visual C++ has discovered no CE platform SDK installed on the d
- 工作感言