SQL Server中列转换 Pivot UnPivot

PIVOT用于将列值旋转也列名(即行转列),在SQLServer 2000可据此聚合函数配合CASE语词实现

PIVOT的形似语法是:PIVOT(聚合函数(列)FOR 列 in (…) )AS
P

完语法:

table_source

PIVOT(

聚合函数(value_column)

FORpivot_column

IN()

)

 

UNPIVOT用于将列明转为列值(即列转行),在SQL Server2000能够据此UNION来兑现

全部语法:

table_source

UNPIVOT(

value_column

FORpivot_column

IN()

)

 

留意:PIVOT、UNPIVOT是SQL Server2005 的语法,使用得修改数据库兼容级别
 在数据库属性->选项->兼容级别改呢 
90

 

独立实例

一、行转列

1、建立表格

ifobject_id(‘tb’)isnotnulldroptabletb

go

createtabletb(姓名varchar(10),课程varchar(10),分数int)

insertintotbvalues(‘张三’,’语文’,74)

insertintotbvalues(‘张三’,’数学’,83)

insertintotbvalues(‘张三’,’物理’,93)

insertintotbvalues(‘李四’,’语文’,74)

insertintotbvalues(‘李四’,’数学’,84)

insertintotbvalues(‘李四’,’物理’,94)

go

select*fromtb

go

姓名       课程       分数



张三       语文       74

张三       数学       83

张三       物理       93

李四       语文       74

李四       数学       84

李四       物理       94

 

2、使用SQLServer
2000静态SQL

–c

select姓名,

 max(case课程when’语文’then分数else0end)语文,

 max(case课程when’数学’then分数else0end)数学,

 max(case课程when’物理’then分数else0end)物理

fromtb

groupby姓名

姓名       语文        数学        物理



李四       74         84         94

张三       74         83         93

 

3、使用SQLServer
2000动态SQL

–SQLSERVER
2000动态SQL,指科目不止语文、数学、物理就三流派科目。(以下同)

–变量按sql语言顺序赋值

declare@sqlvarchar(500)

set@sql=’select姓名’

select@sql=@sql+’,max(case课程when”’+课程+”’ then分数else0
end)[‘+课程+’]’

from(selectdistinct课程fromtb)a–同fromtb
group by课程,默认按学科名为排序

set@sql=@sql+’from tb group by姓名’

exec(@sql)

 

–以isnull(),变量先确定动态部分

declare@sqlvarchar(8000)

select@sql=isnull(@sql+’,’,”)+’max(case课程when”’+课程+”’then分数else0 end)
[‘+课程+’]’

from(selectdistinct课程fromtb)asa      

set@sql=’select姓名,’+@sql+’from tb group
by姓名’

exec(@sql)

姓名       数学        物理        语文



李四       84         94         74

张三       83         93         74

 

4、使用SQLServer
2005静态SQL

select*fromtb pivot(max(分数)for课程in(语文,数学,物理))a

 

5、使用SQL
Server2005动态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、使用SQLServer
2000静态SQL

–SQLSERVER
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、使用SQLServer
2000动态SQL

–SQLSERVER
2000动态SQL

declare@sqlvarchar(500)

set@sql=’select姓名’

select@sql=@sql+’,max(case课程when”’+课程+”’ then分数else0
end)[‘+课程+’]’

from(selectdistinct课程fromtb)a

set@sql=@sql+’,sum(分数)总分,cast(avg(分数*1.0)as
decimal(18,2))     平均分fromtb
group by姓名’

exec(@sql)

 

3、使用SQLServer
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、使用SQLServer
2005动态SQL

–使用stuff()

declare@sqlvarchar(8000)

set@sql=”  –先导化变量@sql

select@sql=@sql+’,’+课程fromtbgroupby课程–变量多值赋值

–同select@sql
= @sql + ‘,’+课程from(select
distinct课程fromtb)a

set@sql=stuff(@sql,1,1,”)–去丢首只’,’

set@sql=’selectm.* ,
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))平均分fromtb
group by姓名)n

wherem.姓名=n.姓名’

exec(@sql)

 

–或使用isnull()

declare@sqlvarchar(8000)

select@sql=isnull(@sql+’,’,”)+课程fromtbgroupby课程

set@sql=’selectm.* ,
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))平均分fromtb
group by姓名)n

wherem.姓名=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、使用SQLServer
2000静态SQL

–SQLSERVER
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、使用SQLServer
2000动态SQL

–SQLSERVER
2000动态SQL。

–调用系统表动态生态。

declare@sqlvarchar(8000)

select@sql=isnull(@sql+’union all ‘,”)+’select姓名,[课程]=’

+quotename(Name,””)+’, [分数]=
‘+quotename(Name)+’ fromtb’

fromsyscolumns

whereName!=’姓名’andID=object_id(‘tb’)–表名tb,不带有列名为真名的其它列

orderbycolid

exec(@sql+’order
by姓名’)

go

 

3、使用SQLServer
2005静态SQL

–SQLSERVER
2005动态SQL

select姓名,课程,分数fromtb unpivot (分数for课程in([语文],[数学],[物理])) t

 

4、使用SQLServer
2005动态SQL

–SQLSERVER
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)

拖欠作品经过自己整理所得,欢迎转载,转载时哀求加上本文地址;本文基于签约2.5
中国陆地
许可协议宣布,请无演绎或用于商业目的,但是得保留本文的签署张志涛

相关文章