SQLServer中行列转换

SQL Server中行列转换 Pivot UnPivot

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

研究到这里，我的同事突然提出一个问题，如果原始数据是下表这样的，我们该如何转置

我们研究了一下，首先，要转置的列依然是课程，但是多了【班级】和【等级评定】两列，而【班级】列的性质与【姓名】类似，【等级评定】列的性质与【分数】列类似，所以，这里我们做如下定义

主键列：例如上表【班级】、【姓名】

转置列：例如上表【课程】

内容列：例如上表【分数】、【等级评定】

先试着手动转置一下看看

我们发现，转置列【语文】、【数学】、【物理】很容易分配，直接转成标题（字段名）即可。转的同时，主键列【班级】和【姓名】作为一组主键进行了合并同类项（当然这里说的主键并非真正的表的主键）。但是接下来问题出现了，我们的内容列有两个，而空位只有一个，没有办法填写，因为横向上一组主键和纵向上一门课程，两条直线只能交叉出一点，所以不能转换。换句话说一组主键在一个科目内不能有多个属性或者内容，否则转置不能。

简单总结一下可以转置的条件：

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 并作简单分析