SQL Server中行列轉(zhuǎn)換 Pivot UnPivot_百度文庫

1、PIVOT用于將列值旋轉(zhuǎn)為列名（即行轉(zhuǎn)列），在SQL Server 2000可以用聚合函數(shù)配合CASE語句實(shí)現(xiàn)PIVOT的一般語法是：PIVOT(聚合函數(shù)(列 FOR 列 in ( AS P完整語法：table_sourcePIVOT(聚合函數(shù)（value_column）FOR pivot_columnIN( UNPIVOT用于將列明轉(zhuǎn)為列值（即列轉(zhuǎn)行），在SQL Server 2000可以用UNION來實(shí)現(xiàn)完整語法：table_sourceUNPIVOT(value_columnFOR pivot_columnIN( 注意：PIVOT、UNPIVOT是SQL Server 2005 的語法，

2、使用需修改數(shù)據(jù)庫兼容級別在數(shù)據(jù)庫屬性->選項(xiàng)->兼容級別改為 90典型實(shí)例一、行轉(zhuǎn)列1、建立表格ifobject_id('tb'isnotnulldroptabletbgocreatetabletb(姓名varchar(10,課程varchar(10,分?jǐn)?shù)intinsertintotbvalues('張三','語文',74insertintotbvalues('張三','數(shù)學(xué)',83insertintotbvalues('張三','物理',93insertintotbva

3、lues('李四','語文',74insertintotbvalues('李四','數(shù)學(xué)',84insertintotbvalues('李四','物理',94goselect*fromtbgo姓名 課程 分?jǐn)?shù)- - -張三 語文 74張三 數(shù)學(xué) 83張三 物理 93李四 語文 74李四 數(shù)學(xué) 84李四 物理 942、使用SQL Server 2000靜態(tài)SQL-cselect姓名,max(case課程when'語文'then分?jǐn)?shù)else0end語文,max(case課程when

4、9;數(shù)學(xué)'then分?jǐn)?shù)else0end數(shù)學(xué),max(case課程when'物理'then分?jǐn)?shù)else0end物理fromtbgroupby姓名姓名 語文 數(shù)學(xué) 物理- - - -李四 74 84 94張三 74 83 933、使用SQL Server 2000動(dòng)態(tài)SQL-SQL SERVER 2000動(dòng)態(tài)SQL,指課程不止語文、數(shù)學(xué)、物理這三門課程。(以下同-變量按sql語言順序賦值declaresqlvarchar(500setsql='select姓名'selectsql=sql+',max(case課程when '''

5、;+課程+''' then分?jǐn)?shù)else 0 end'+課程+''from(selectdistinct課程fromtba-同from tb group by課程，默認(rèn)按課程名排序setsql=sql+' from tb group by姓名'exec(sql-使用isnull(,變量先確定動(dòng)態(tài)部分declaresqlvarchar(8000selectsql=isnull(sql+',',''+' max(case課程when '''+課程+''

6、9; then分?jǐn)?shù)else 0 end '+課程+''from(selectdistinct課程fromtbasa setsql='select姓名,'+sql+' from tb group by姓名'exec(sql姓名 數(shù)學(xué) 物理 語文- - - -李四 84 94 74張三 83 93 744、使用SQL Server 2005靜態(tài)SQLselect*fromtb pivot(max(分?jǐn)?shù)for課程in(語文,數(shù)學(xué),物理a5、使用SQL Server 2005動(dòng)態(tài)SQL-使用stuff(declaresqlvarchar(8000

7、setsql='' -初始化變量sqlselectsql=sql+','+課程fromtbgroupby課程-變量多值賦值setsql=stuff(sql,1,1,''-去掉首個(gè)','setsql='select * from tb pivot (max(分?jǐn)?shù) for課程in ('+sql+'a'exec(sql-或使用isnull(declaresqlvarchar(8000-獲得課程集合selectsql=isnull(sql+',',''+課程fromtbgro

8、upby課程 setsql='select * from tb pivot (max(分?jǐn)?shù) for課程in ('+sql+'a'exec(sql二、行轉(zhuǎn)列結(jié)果加上總分、平均分1、使用SQL Server 2000靜態(tài)SQL-SQL SERVER 2000靜態(tài)SQLselect姓名,max(case課程when'語文'then分?jǐn)?shù)else0end語文,max(case課程when'數(shù)學(xué)'then分?jǐn)?shù)else0end數(shù)學(xué),max(case課程when'物理'then分?jǐn)?shù)else0end物理,sum(分?jǐn)?shù)總分,cast(

9、avg(分?jǐn)?shù)*1.0asdecimal(18,2平均分fromtbgroupby姓名姓名 語文 數(shù)學(xué) 物理 總分 平均分- - - - -李四 74 84 94 252 84.00張三 74 83 93 250 83.332、使用SQL Server 2000動(dòng)態(tài)SQL-SQL SERVER 2000動(dòng)態(tài)SQLdeclaresqlvarchar(500setsql='select姓名'selectsql=sql+',max(case課程when '''+課程+''' then分?jǐn)?shù)else 0 end'+課程+

10、9;'from(selectdistinct課程fromtbasetsql=sql+',sum(分?jǐn)?shù)總分,cast(avg(分?jǐn)?shù)*1.0 as decimal(18,2 平均分from tb group by姓名'exec(sql3、使用SQL Server 2005靜態(tài)SQLselectm.*,n.總分,n.平均分from(select*fromtb pivot(max(分?jǐn)?shù)for課程in(語文,數(shù)學(xué),物理am,(select姓名,sum(分?jǐn)?shù)總分,cast(avg(分?jǐn)?shù)*1.0asdecimal(18,2平均分fromtbgroupby姓名nwherem.姓名=n.

11、姓名4、使用SQL Server 2005動(dòng)態(tài)SQL-使用stuff(-declaresqlvarchar(8000setsql='' -初始化變量sqlselectsql=sql+','+課程fromtbgroupby課程-變量多值賦值-同select sql = sql + ','+課程from (select distinct課程from tbasetsql=stuff(sql,1,1,''-去掉首個(gè)','setsql='select m.* , n.總分,n.平均分from(select * fro

12、m (select * from tb a pivot (max(分?jǐn)?shù) for課程in ('+sql+' b m ,(select姓名,sum(分?jǐn)?shù)總分, cast(avg(分?jǐn)?shù)*1.0 as decimal(18,2平均分from tb group by姓名 nwhere m.姓名= n.姓名'exec(sql-或使用isnull(declaresqlvarchar(8000selectsql=isnull(sql+',',''+課程fromtbgroupby課程setsql='select m.* , n.總分,n.平均分fr

13、om(select * from (select * from tb a pivot (max(分?jǐn)?shù) for課程in ('+sql+' b m ,(select姓名,sum(分?jǐn)?shù)總分, cast(avg(分?jǐn)?shù)*1.0 as decimal(18,2平均分from tb group by姓名 nwhere m.姓名= n.姓名'exec(sql二、列轉(zhuǎn)行1、建立表格ifobject_id('tb'isnotnulldroptabletbgocreatetabletb(姓名varchar(10,語文int,數(shù)學(xué)int,物理intinsertintotbval

14、ues('張三',74,83,93insertintotbvalues('李四',74,84,94goselect*fromtbgo姓名 語文 數(shù)學(xué) 物理- - - -張三 74 83 93李四 74 84 942、使用SQL Server 2000靜態(tài)SQL-SQL SERVER 2000靜態(tài)SQL。select*from(select姓名,課程='語文',分?jǐn)?shù)=語文fromtbunionallselect姓名,課程='數(shù)學(xué)',分?jǐn)?shù)=數(shù)學(xué)fromtbunionallselect姓名,課程='物理',分?jǐn)?shù)=物理fr

15、omtbtorderby姓名,case課程when'語文'then1when'數(shù)學(xué)'then2when'物理'then3end姓名 課程 分?jǐn)?shù)- - -李四 語文 74李四 數(shù)學(xué) 84李四 物理 94張三 語文 74張三 數(shù)學(xué) 83張三 物理 932、使用SQL Server 2000動(dòng)態(tài)SQL-SQL SERVER 2000動(dòng)態(tài)SQL。-調(diào)用系統(tǒng)表動(dòng)態(tài)生態(tài)。declaresqlvarchar(8000selectsql=isnull(sql+' union all ',''+' select姓名, 課程=

16、'+quotename(Name,''''+' , 分?jǐn)?shù) = '+quotename(Name+' from tb'fromsyscolumnswhereName!='姓名'andID=object_id('tb'-表名tb，不包含列名為姓名的其他列orderbycolidexec(sql+' order by姓名'go3、使用SQL Server 2005靜態(tài)SQL-SQL SERVER 2005動(dòng)態(tài)SQLselect姓名,課程,分?jǐn)?shù)fromtb unpivot (分?jǐn)?shù)for課程in(語文,數(shù)學(xué),物理 t4、使用SQL Server 2005動(dòng)態(tài)SQL-SQL SERVER 20