MySql 行轉列 與 列轉行

文章轉載位址:https://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html

SQL Server中行列轉換 Pivot UnPivot

PIVOT用于将列值旋轉為列名（即行轉列），在SQL Server 2000可以用聚合函數配合CASE語句實作

PIVOT的一般文法是：PIVOT(聚合函數(列) FOR 列 in (…) )AS P

完整文法：

table_source

PIVOT(

聚合函數（value_column）

FOR pivot_column

IN(<column_list>)

)

UNPIVOT用于将列明轉為列值（即列轉行），在SQL Server 2000可以用UNION來實作

完整文法：

table_source

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)

注意：PIVOT、UNPIVOT是SQL Server 2005 的文法，使用需修改資料庫相容級别

 在資料庫屬性->選項->相容級别改為   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、使用SQL Server 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、使用SQL Server 2000動态SQL

–SQL SERVER 2000動态SQL,指課程不止國文、數學、實體這三門課程。(以下同)

–變量按sql語言順序指派

[email protected](500)

[email protected]=‘select姓名’

[email protected][email protected]+‘,max(case課程when ”’+課程+”’ then分數else 0 end)[‘+課程+‘]’

from(selectdistinct課程fromtb)a–同from tb group by課程，預設按課程名排序

[email protected][email protected]+’ from tb group by姓名’

exec(@sql)

–使用isnull(),變量先确定動态部分

[email protected](8000)

[email protected]=isnull(@sql+‘,’,”)+’ max(case課程when ”’+課程+”’ then分數else 0 end) [‘+課程+’]’

from(selectdistinct課程fromtb)asa      

[email protected]=‘select姓名,’[email protected]+’ 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()

[email protected](8000)

[email protected]=”  –初始化變量@sql

[email protected][email protected]+‘,’+課程fromtbgroupby課程–變量多值指派

[email protected]=stuff(@sql,1,1,”)–去掉首個‘,’

[email protected]=‘select * from tb pivot (max(分數) for課程in (‘[email protected]+‘))a’

exec(@sql)

–或使用isnull()

[email protected](8000)

–-獲得課程集合

[email protected]=isnull(@sql+‘,’,”)+課程fromtbgroupby課程           

[email protected]=‘select * from tb pivot (max(分數) for課程in (‘[email protected]+‘))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

[email protected](500)

[email protected]=‘select姓名’

[email protected][email protected]+‘,max(case課程when ”’+課程+”’ then分數else 0 end)[‘+課程+‘]’

from(selectdistinct課程fromtb)a

[email protected][email protected]+‘,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()

–

[email protected](8000)

[email protected]=”  –初始化變量@sql

[email protected][email protected]+‘,’+課程fromtbgroupby課程–變量多值指派

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

[email protected]=stuff(@sql,1,1,”)–去掉首個‘,’

[email protected]=‘select m.* , n.總分,n.平均分from

(select * from (select * from tb) a pivot (max(分數) for課程in (‘[email protected]+‘)) b) m ,

(select姓名,sum(分數)總分, cast(avg(分數*1.0) as decimal(18,2))平均分from tb group by姓名) n

where m.姓名= n.姓名’

exec(@sql)

–或使用isnull()

[email protected](8000)

[email protected]=isnull(@sql+‘,’,”)+課程fromtbgroupby課程

[email protected]=‘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。

–調用系統表動态生态。

[email protected](8000)

[email protected]=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

[email protected](4000)

[email protected]=isnull(@sql+‘,’,”)+quotename(Name)

fromsyscolumns

whereID=object_id(‘tb’)andNamenotin(’姓名’)

orderbyColid

[email protected]=‘select姓名,[課程],[分數] from tb unpivot ([分數] for [課程] in(‘[email protected]+‘))b’

exec(@sql)