来自: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,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('李四',74,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)