SQLServer中行列转换Pivot UnPivot重点讲义资料Word文件下载.docx

SQLServer中行列转换Pivot UnPivot重点讲义资料Word文件下载.docx

《SQLServer中行列转换Pivot UnPivot重点讲义资料Word文件下载.docx》由会员分享，可在线阅读，更多相关《SQLServer中行列转换Pivot UnPivot重点讲义资料Word文件下载.docx（8页珍藏版）》请在冰豆网上搜索。

选项->

兼容级别改为 

90

典型实例

一、行转列

1、建立表格

ifobject_id（'

tb'

）isnotnulldroptabletb

go

createtabletb（姓名varchar（10）,课程varchar（10）,分数int）

insertintotbvalues（'

张三'

'

语文'

74）

数学'

83）

物理'

93）

李四'

84）

94）

select*fromtb

姓名 

课程 

分数

-------------------------------

张三 

语文 

74

数学 

83

物理 

93

李四 

84

94

2、使用SQLServer2000静态SQL

--c

select姓名,

max（case课程when'

then分数else0end）语文,

then分数else0end）数学,

then分数else0end）物理

fromtb

groupby姓名

物理

-------------------------------------------

74 

84 

83 

3、使用SQLServer2000动态SQL

--SQLSERVER2000动态SQL,指课程不止语文、数学、物理这三门课程。

（以下同）

--变量按sql语言顺序赋值

declare@sqlvarchar（500）

set@sql='

select姓名'

select@sql=@sql+'

max（case课程when'

'

+课程+'

then分数else0end）['

]'

from（selectdistinct课程fromtb）a--同fromtbgroupby课程，默认按课程名排序

set@sql=@sql+'

fromtbgroupby姓名'

exec（@sql）

--使用isnull（）,变量先确定动态部分

declare@sqlvarchar（8000）

select@sql=isnull（@sql+'

）+'

max（case课程when'

then分数else0end）['

from（selectdistinct课程fromtb）asa 

select姓名,'

+@sql+'

语文

94 

93 

4、使用SQLServer2005静态SQL

select*fromtb 

pivot（max（分数）for课程in（语文,数学,物理））a

5、使用SQLServer2005动态SQL

--使用stuff（）

--初始化变量@sql

+课程fromtbgroupby课程--变量多值赋值

set@sql=stuff（@sql,1,1,'

）--去掉首个'

select*fromtb 

pivot（max（分数）for课程in（'

））a'

--或使用isnull（）

–-获得课程集合

）+课程fromtbgroupby课程 

二、行转列结果加上总分、平均分

1、使用SQLServer2000静态SQL

--SQLSERVER2000静态SQL

then分数else0end）物理,

sum（分数）总分,

cast（avg（分数*1.0）asdecimal（18,2））平均分

总分 

平均分

------------------------------------------------------

252 

84.00

250 

83.33

2、使用SQLServer2000动态SQL

--SQLSERVER2000动态SQL

from（selectdistinct课程fromtb）a

sum（分数）总分,cast（avg（分数*1.0）asdecimal（18,2）） 

平均分fromtbgroupby姓名'

3、使用SQLServer2005静态SQL

selectm.*,n.总分,n.平均分

from

（select*fromtb 

pivot（max（分数）for课程in（语文,数学,物理））a）m,

（select姓名,sum（分数）总分,cast（avg（分数*1.0）asdecimal（18,2））平均分

groupby姓名）n

wherem.姓名=n.姓名

4、使用SQLServer2005动态SQL

--

--同select@sql=@sql+'

+课程from（selectdistinct课程fromtb）a

selectm.*,n.总分,n.平均分from

（select*from（select*fromtb）apivot（max（分数）for课程in（'

））b）m,

（select姓名,sum（分数）总分,cast（avg（分数*1.0）asdecimal（18,2））平均分fromtbgroupby姓名）n

wherem.姓名=n.姓名'

）+课程fromtbgroupby课程

+

@sql+'

二、列转行

createtabletb（姓名varchar（10）,语文int,数学int,物理int）

74,83,93）

74,84,94）

74 

--SQLSERVER2000静态SQL。

select*from

（

select姓名,课程='

分数=语文fromtb

unionall

分数=数学fromtb

分数=物理fromtb

）t

orderby姓名,case课程when'

then1when'

then2when'

then3end

-------------------------

74

84

94

83

93

--SQLSERVER2000动态SQL。

--调用系统表动态生态。

unionall'

select姓名,[课程]='

+quotename（Name,'

[分数]='

+quotename（Name）+'

fromtb'

fromsyscolumns

whereName!

='

姓名'

andID=object_id（'

）--表名tb，不包含列名为姓名的其他列

orderbycolid

exec（@sql+'

orderby姓名'

--SQLSERVER2005动态SQL

select姓名,课程,分数fromtbunpivot（分数for课程in（[语文],[数学],[物理]））t

declare@sqlnvarchar（4000）

）+quotename（Name）

whereID=object_id（'

）andNamenotin（'

orderbyColid

select姓名,[课程],[分数]fromtbunpivot（[分数]for[课程]in（'

））b'