|
来自：http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html?感谢！
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tb'))
drop table #tb
--一、行转列
--1、建立表格
? create table #tb(stu_name varchar(10),course varchar(10),score int)
?insert into #tb values('张三','语文',74)
?insert into #tb values('张三','数学',83)
?insert into #tb values('张三','物理',93)
?insert into #tb values('李四',74)
?insert into #tb values('李四',84)
?insert into #tb values('李四',94)
?go
?--查询临时表
?select * from #tb
--2、使用SQL Server 2000静态SQL
?select
? stu_name 姓名,
? max(case course when'语文' then score else 0? end)语文,
? max(case course when'数学' then score else 0 end)数学,
? max(case course when'物理' then score else 0 end)物理
?from #tb
?group by stu_name?
--3、使用SQL Server 2000动态SQL
--SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
--变量按sql语言顺序赋值(有问题)
declare @sql varchar(500)
set @sql='select stu_name'
select @sql=@sql+',max(case course when '''+course+''' then course else 0 end)['+course+']'
from(select distinct course from #tb)a --同from tb group by课程，默认按课程名排序
print @sql
set @sql=@sql+' from #tb group by stu_name'
print @sql
exec(@sql)
--使用isnull(),变量先确定动态部分
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+' max(case course when '''+course+''' then score else 0 end) ['+course+']'
from(select distinct course from #tb)as a??
set @sql='select stu_name 姓名,'+@sql+' from #tb group by stu_name'
exec(@sql)
--4、使用SQL Server 2005静态SQL
select * from #tb
?pivot(
?? max(score)for course in (语文,数学,物理)
?)t
--5、使用SQL Server 2005动态SQL
--使用stuff()
declare @sql varchar(8000)
set @sql=''? --初始化变量@sql
select @sql=@sql+','+course from #tb group by course--变量多值赋值
set @sql=stuff(@sql,1,'')--去掉首个','
set @sql='select * from #tb pivot (max(score) for course in ('+@sql+'))a'
exec(@sql)
--或使用isnull()
declare @sql varchar(8000)
----获得课程集合
select @sql=isnull(@sql+','')+course from #tb group by course?
set @sql='select * from #tb pivot (max(score) for course in ('+@sql+'))a'
exec(@sql)
--6、行转列结果加上总分、平均分
select stu_name,
max(case course when'语文'then score else 0 end)语文,
max(case course when'数学'then score else 0 end)数学,
max(case course when'物理'then score else 0 end)物理,
sum(score)总分,
cast(avg(score*1.0)as decimal(18,2))平均分,
CONVERT(decimal(18,2),avg(score*1.0))平均分01
from #tb?
group by stu_name
--二、列转行
--1、建立表格
if object_id('#tb1')is not null
? drop table #tb1?
create table #tb1(姓名 varchar(10),语文 int,数学 int,物理 int)
insert into #tb1 values('张三',74,83,93)
insert into #tb1 values('李四',84,94)
go
select*from #tb1
--2、使用SQL Server 2000静态SQL
--SQL SERVER 2000静态SQL。
select * from
(
?select 姓名,课程='语文',分数=语文 from #tb1
?union all
?select 姓名,课程='数学',分数=数学 from #tb1
?union all
?select 姓名,课程='物理',分数=物理 from #tb1
) t
order by 姓名,case 课程 when'语文'then 1 when'数学'then 2 when'物理'then 3 end
--2、使用SQL Server 2000动态SQL
--SQL SERVER 2000动态SQL。
--调用系统表动态生态。 (有问题)
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select 姓名,[课程]='
+quotename(Name,'''')+',[分数] = '+quotename(Name)+' from #tb1'
from syscolumns
where Name!='姓名' and ID=object_id('#tb1')--表名，不包含列名为姓名的其他列
order by colid
select @sql=@sql+' order by 姓名'
print @sql
exec(@sql+' order by 姓名')
select * from #tb1
--3、使用SQL Server 2005静态SQL
--SQL SERVER 2005动态SQL
select 姓名,课程,分数 from #tb1 unpivot (分数 for 课程 in([语文],[数学],[物理])) t
--4、使用SQL Server 2005动态SQL
--SQL SERVER 2005动态SQL
declare @sql nvarchar(4000)
select @sql=isnull(@sql+','')+quotename(Name)
from syscolumns
where ID=object_id('#tb1')and Name not in('姓名')
order by Colid
set @sql='select 姓名,[课程],[分数] from #tb1 unpivot ([分数] for [课程] in('+@sql+'))b' exec(@sql)
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
