PIVOT用于将列值旋转为列名(即行转列)，在SQL
Server 2000可以用聚合函数配合CASE语句实现
PIVOT的一般语法是：PIVOT(聚合函数(列)
FOR 列 in (…) )AS P
完整语法：
table_source
PIVOT(
聚合函数(value_column)
FOR
pivot_column
IN()
)
UNPIVOT用于将列名转为列值(即列转行)，在SQL
Server 2000可以用UNION来实现
完整语法：
table_source
UNPIVOT(
value_column
FOR
pivot_column
IN()
)
注意：PIVOT、UNPIVOT是SQL
Server 2005 的语法，使用需修改数据库兼容级别在数据库属性->选项->兼容级别改为
90
典型实例
一、行转列
1、建立表格
if
object_id('tb') is not null drop table tb
go
create
table tb (姓名varchar(10),课程varchar(10),分数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
go
姓名课程分数
---------- ----------
-----------
张三语文74
张三数学83
张三物理93
李四语文74
李四数学84
李四物理94
2、使用SQL Server
2000静态SQL
select 姓名,
max(case
课程
when '语文'
then 分数
else 0 end) 语文,
max(case
课程
when '数学'
then 分数
else 0 end) 数学,
max(case
课程
when '物理'
then 分数
else 0 end) 物理
from tb
group by 姓名
姓名语文数学物理
---------- -----------
----------- -----------
李四74 84 94
张三74 83 93
其思路是：
1.判断是将哪一列进行转置。例如本例，可以判断是将原表【课程】的列值转置为列名。
2.判断转置列转置之后其他列会发生什么变化。例如本例，转置后【姓名】列会合并同类项，这里其实也暗示了将来要GROUP
BY的对象，但分析到现在还不明朗。
3.用最简单的select-from-where
试试能不能输出一条像转置后模样的记录。例如本例，这里会很自然地构造出如下SQL文
select 姓名,
case 课程when
'语文'
then 分数end
as '语文',
case 课程when
'数学'
then 分数end
as '数学',
case 课程when
'物理'
then 分数end
as '物理'
from tb
where 姓名=
'张三'
得到如下结果：
姓名
语文
数学
物理
张三
74 NULL NULL
张三
NULL 83 NULL
张三
NULL NULL 93
那么从上述想法和结果我们可以判断出一点--
我们想到的where条件是【姓名】，因为我们做初始筛选的时候要将对象限定在一个人也就是一个姓名上才方便做下一步转化，换句话说我们写出的这个模型就是整表转置的一个一部分,就像分了一个特定小组并在组内进行的试验性研究。推而广之,我们要将全部组都囊括，就不能通过where来只限定一个小组，而应该使用group
by进行分组，而group
by的条件很自然地就是where句中的字段。
所以，我们又有了如下的微调。
select 姓名,
case 课程when
'语文'
then 分数end
as '语文',
case 课程when
'数学'
then 分数end
as '数学',
case 课程when
'物理'
then 分数end
as '物理'
from tb
group by 姓名
并期待如下结果
姓名
语文
数学
物理
张三
74 NULL NULL
张三
NULL 83 NULL
张三
NULL NULL 93
李四
74 NULL NULL
李四
NULL 84 NULL
李四
NULL NULL 94
当然事实是我们得到的是如下错误
消息8120，级别16，状态1，第2
行
选择列表中的列'tb.课程'
无效，因为该列没有包含在聚合函数或GROUP
BY 子句中。
消息8120，级别16，状态1，第2
行
选择列表中的列'tb.分数'
无效，因为该列没有包含在聚合函数或GROUP
BY 子句中。
这里涉及到group
by的常识性语法，我们不做详解，但总之,我们要做的只剩下两件事：第一消除语法错误，第二将上述想定结果的null值删掉，将有用数据合并。
而这两件事的解决方法非常巧妙，是需要灵感或者聪明的头脑或者对SQL的感觉的。
其巧妙之处是两个问题是通过一种类型的改动一次性解决的。
我们接下来尽量试着推一推其步骤：
a.从消除错误的角度，我们有两个想法，第一、在select句中加入【姓名】字段，离想定结果越走越远，否定。第二、给分数加上min、avg、max、count之类的聚簇函数，这里我们认为max比较值得一试
select 姓名,
max(case 课程
when '语文'
then 分数
end)
as '语文',
max(case 课程
when '数学'
then 分数
end)
as '数学',
max(case 课程
when '物理'
then 分数
end)
as '物理'
from tb
group by 姓名
并意外得到如下结果
姓名
语文
数学
物理
李四
74 84 94
张三
74 83 93
并且为了安全性，再做一些微调，得出如下SQL文：
select 姓名,
max(case
课程
when '语文'
then 分数
else 0 end) 语文,
max(case
课程
when '数学'
then 分数
else 0 end) 数学,
max(case
课程
when '物理'
then 分数
else 0 end) 物理
from tb
group by 姓名
b.从消除null的角度，我们先观察，我们要消除的是同一位同学的同一门课的null值，并且除了null值之外该同学还有一个真正的分数值，一个是数字一个是null，可以用isnull函数，但是使用之后又该怎么办，此路不通，那么干脆把null全替换成0，再用max，这样就殊途同归了。
select 姓名,
max(case
课程
when '语文'
then 分数
else 0 end) 语文,
max(case
课程
when '数学'
then 分数
else 0 end) 数学,
max(case
课程
when '物理'
then 分数
else 0 end) 物理
from tb
group by 姓名
3、使用SQL Server 2000动态SQL
--SQL
SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
--变量按sql语言顺序赋值
declare @sql
varchar(500) --定义变量存储SQL文
set @sql = 'select
姓名' --给变量赋HEAD部
select @sql = @sql + ',max( case 课程 when ''' + 课程 + ''' then 分数
else 0 end)['+课程+']' --给变量赋BODY部，该行要关注的是三个单引号，为外层的单引号表示其内部为字符串，另外两个中的第一个为转义字符，两个合起来表示这里的内容就是一个单引号，三个单引号合起来表示这是一个字符串，串内有个单引号
from(select distinct 课程 from tb) a --注意：该行作用仅作为提供有多少门课程，在执行过程中，会拿每一门课程内容(比如'语文')去替换上一行中的课程二字，当然上行中的第一个课程二字指的是字段名，这里SQL
SERVER会进行智能判断，可以替换的才会替换，并且貌似只能在使用赋值语句且为使用select进行赋值时(如上一行样)才可以使用类似该行的方法
set @sql = @sql + ' from tb group by
姓名' --给变量赋FOOT部
exec(@sql)
其在实际执行时会拼成如下SQL文，会发现和之前的静态SQL一样
select 姓名,
max(case 课程
when '语文'
then 分数
end)
as '语文',
max(case 课程
when '数学'
then 分数
end)
as '数学',
max(case 课程
when '物理'
then 分数
end)
as '物理'
from tb
group by 姓名
--使用isnull(),变量先确定动态部分
declare@sqlvarchar(8000)
select@sql=isnull(@sql+',','')+'
max(case课程when
'''+课程+'''
then分数else
0 end) ['+课程+']'
from(selectdistinct课程fromtb)asa
set@sql='select姓名,'+@sql+'
from tb group by姓名'
exec(@sql)
姓名数学物理语文
---------- -----------
----------- -----------
李四84 94 74
张三83 93 74
研究到这里，我的同事突然提出一个问题，如果原始数据是下表这样的，我们该如何转置
班级
姓名
课程
分数
等级评定
071
张三
语文
74
C
071
张三
数学
83
B
071
张三
物理
93
A
072
李四
语文
74
C
072
李四
数学
84
B
072
李四
物理
94
A
071
李四
语文
75
C
071
李四
数学
85
B
071
李四
物理
95
A
我们研究了一下，首先，要转置的列依然是课程，但是多了【班级】和【等级评定】两列，而【班级】列的性质与【姓名】类似，【等级评定】列的性质与【分数】列类似，所以，这里我们做如下定义
主键列：例如上表【班级】、【姓名】
转置列：例如上表【课程】
内容列：例如上表【分数】、【等级评定】
先试着手动转置一下看看
班级
姓名
语文
数学
物理
071
张三
071
李四
072
李四
我们发现，转置列【语文】、【数学】、【物理】很容易分配，直接转成标题(字段名)即可。转的同时，主键列【班级】和【姓名】作为一组主键进行了合并同类项(当然这里说的主键并非真正的表的主键)。但是接下来问题出现了，我们的内容列有两个，而空位只有一个，没有办法填写，因为横向上一组主键和纵向上一门课程，两条直线只能交叉出一点，所以不能转换。换句话说一组主键在一个科目内不能有多个属性或者内容，否则转置不能。
简单总结一下可以转置的条件：
主键列
可多项
其字段名是GROUP BY 条件
转置列
理论上可多项，但多项很复杂
其转置前字段名是CASE条件；其内容是WHEN条件
内容列
只可一项
其内容是THEN内容
4、使用SQL Server 2005静态SQL
select*fromtb pivot(max(分数)for课程in(语文,数学,物理))a
5、使用SQL Server
2005动态SQL
--使用stuff()
declare@sqlvarchar(8000)
set@sql='' --初始化变量@sql
select@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值
set@sql=stuff(@sql,1,1,'')--去掉首个','
set@sql='select
* from tb pivot (max(分数)
for课程in
('+@sql+'))a'
exec(@sql)
--或使用isnull()
declare@sqlvarchar(8000)
–-获得课程集合
select@sql=isnull(@sql+',','')+课程fromtbgroupby课程
set@sql='select
* from tb pivot (max(分数)
for课程in
('+@sql+'))a'
exec(@sql)
二、行转列结果加上总分、平均分
1、使用SQL Server 2000静态SQL
--SQL
SERVER 2000静态SQL
select姓名,
max(case课程when'语文'then分数else0end)语文,
max(case课程when'数学'then分数else0end)数学,
max(case课程when'物理'then分数else0end)物理,
sum(分数)总分,
cast(avg(分数*1.0)asdecimal(18,2))平均分
fromtb
groupby姓名
姓名语文数学物理总分平均分
---------- -----------
----------- ----------- -----------
李四74 84 94 252 84.00
张三74 83 93 250 83.33
2、使用SQL Server 2000动态SQL
--SQL
SERVER 2000动态SQL
declare@sqlvarchar(500)
set@sql='select姓名'
select@sql=@sql+',max(case课程when
'''+课程+''' then分数else
0 end)['+课程+']'
from(selectdistinct课程fromtb)a
set@sql=@sql+',sum(分数)总分,cast(avg(分数*1.0)
as decimal(18,2))平均分from
tb group by姓名'
exec(@sql)
3、使用SQL Server 2005静态SQL
selectm.*,n.总分,n.平均分
from
(select*fromtb pivot(max(分数)for课程in(语文,数学,物理))a)m,
(select姓名,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2))平均分
fromtb
groupby姓名)n
wherem.姓名=n.姓名
4、使用SQL
Server 2005动态SQL
--使用stuff()
--
declare@sqlvarchar(8000)
set@sql='' --初始化变量@sql
select@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值
--同select
@sql = @sql + ','+课程from
(select distinct课程from
tb)a
set@sql=stuff(@sql,1,1,'')--去掉首个','
set@sql='select
m.* , n.总分,n.平均分from
(select *
from (select * from tb) a pivot (max(分数)
for课程in
('+@sql+')) b) m
,
(select姓名,sum(分数)总分,
cast(avg(分数*1.0)
as decimal(18,2))平均分from
tb group by姓名)
n
where
m.姓名=
n.姓名'
exec(@sql)
--或使用isnull()
declare@sqlvarchar(8000)
select@sql=isnull(@sql+',','')+课程fromtbgroupby课程
set@sql='select
m.* , n.总分,n.平均分from
(select *
from (select * from tb) a pivot (max(分数)
for课程in
('+
@sql+'))
b) m ,
(select姓名,sum(分数)总分,
cast(avg(分数*1.0)
as decimal(18,2))平均分from
tb group by姓名)
n
where
m.姓名=
n.姓名'
exec(@sql)
二、列转行
1、建立表格
ifobject_id('tb')isnotnulldroptabletb
go
createtabletb(姓名varchar(10),语文int,数学int,物理int)
insertintotbvalues('张三',74,83,93)
insertintotbvalues('李四',74,84,94)
go
select*fromtb
go
姓名语文数学物理
---------- -----------
----------- -----------
张三74 83 93
李四74 84 94
2、使用SQL Server 2000静态SQL
--SQL
SERVER 2000静态SQL。
select*from
(
select姓名,课程='语文',分数=语文fromtb
unionall
select姓名,课程='数学',分数=数学fromtb
unionall
select姓名,课程='物理',分数=物理fromtb
)
t
orderby姓名,case课程when'语文'then1when'数学'then2when'物理'then3end
姓名课程分数
---------- ----
-----------
李四语文74
李四数学84
李四物理94
张三语文74
张三数学83
张三物理93
2、使用SQL Server 2000动态SQL
--SQL
SERVER 2000动态SQL。
--调用系统表动态生态。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+'
union all ','')+'
select姓名,
[课程]='
+quotename(Name,'''')+'
, [分数]
= '+quotename(Name)+' from
tb'
fromsyscolumns
whereName!='姓名'andID=object_id('tb')--表名tb，不包含列名为姓名的其他列
orderbycolid
exec(@sql+'
order by姓名')
go
3、使用SQL Server 2005静态SQL
--SQL
SERVER 2005动态SQL
select姓名,课程,分数fromtb unpivot (分数for课程in([语文],[数学],[物理])) t
4、使用SQL
Server 2005动态SQL
--SQL
SERVER 2005动态SQL
declare@sqlnvarchar(4000)
select@sql=isnull(@sql+',','')+quotename(Name)
fromsyscolumns
whereID=object_id('tb')andNamenotin('姓名')
orderbyColid
set@sql='select姓名,[课程],[分数]
from tb unpivot ([分数]
for [课程]
in('+@sql+'))b'
exec(@sql)
转自http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html
并作简单分析
扫一扫
197
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
