Sql 行列转换 动态Sql(Pivot)
来源:互联网 发布:linux touch.stmp 编辑:程序博客网 时间:2024/05/18 02:21
--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(Pivot)
- SQL SERVER PIVOT 行列转换
- sql pivot 实现行列转换
- SQL行列转换PIVOT、UNPIVOT
- SQL中PIVOT 行列转换
- SQL中PIVOT 行列转换
- SQL中PIVOT 行列转换
- SQL 2008行列转换的pivot--产生动态列
- SQL server2005中用pivot实现行列转换
- Sql server pivot应用 行列转换
- SQL Server 行列转换 Pivot UnPivot
- sql server 行列转换PIVOT 和 UNPIVOT
- SQL通过PIVOT/UNPIVOT实现行列转换
- sql server(PIVOT) 行列转换 .
- sql server(PIVOT) 行列转换
- sql server(PIVOT) 行列转换
- t-sql中pivot用法(行列转换)
- SQL Server 2005之PIVOT/UNPIVOT行列转换
- C++ 在 Linux下编译动态链接库
- 点评我接触的各种编程语言
- 关机、注销、重启的C++实现 2011.04.22
- vs2008安装失败解决办法
- 解决IE8将“在页面中选中一段文字”误认为是Onclick 事件
- Sql 行列转换 动态Sql(Pivot)
- Work queue 详解 (ZZ)
- c#如何操作excel ?
- may not respond to
- js不重复导入
- js innerHTML outerHTML innerText
- 谢谢好心人
- vbox中设置Ubuntu分辨率为1280x800【转】
- 【转】为何选择 Flex 和 PHP