SQL Server中怎么使用Pivot和UnPivot实现行列转换(pivot,sql server,unpivot,开发技术)

先创建一个用于演示的临时表：

createtable#temp(年份nvarchar(10)null,月份nvarchar(10)null,数量intnull)insertinto#temp(年份,月份,数量)select'2015','1','5645'unionselect'2015','2','1234'unionselect'2015','3','7982'unionselect'2016','1','6465'unionselect'2016','2','7942'unionselect'2016','3','8453'unionselect'2017','1','4653'unionselect'2017','2','1358'unionselect'2017','3','7842'select*from#temp

下面来实现一些需求：

需求一，按年份分组，不同的月份为一列。

--按年份分组，不同的月份为一列selectt.年份,sum(caset.月份when'1'thent.数量end)'1月份',sum(caset.月份when'2'thent.数量end)'2月份',sum(caset.月份when'3'thent.数量end)'3月份'from#temptgroupbyt.年份

另外两种方法：

--使用左外连接查询selectt.年份,t1.数量'1月份',t2.数量'2月份',t3.数量'3月份'from#temptleftjoin(select年份,数量from#tempwhere月份='1')t1ont.年份=t1.年份leftjoin(select年份,数量from#tempwhere月份='2')t2ont.年份=t2.年份leftjoin(select年份,数量from#tempwhere月份='3')t3ont.年份=t3.年份groupbyt.年份,t1.数量,t2.数量,t3.数量--使用自连接查询selectt.年份,t1.数量'1月份',t2.数量'2月份',t3.数量'3月份'from#tempt,(select年份,数量from#tempwhere月份='1')t1,(select年份,数量from#tempwhere月份='2')t2,(select年份,数量from#tempwhere月份='3')t3wheret.年份=t1.年份andt.年份=t2.年份andt.年份=t3.年份groupbyt.年份,t1.数量,t2.数量,t3.数量

返回的结果都是一样的，可以看见这几种方法都是可以实现的（当然，可能还有更多的方法待发掘），不过比起第一种方法，后面这两种方法也太低效了吧，比如一年有12个月份的数据，有个七八年的，那得写多少个子查询、表连接的，而且第一种方法也不是我们想要的。那么就需要用到 Pivot 这种方法了。

Pivot 语法：

table_source--表名称，即数据源PIVOT(聚合函数（value_column）--value_column要转换为列值的列名FORpivot_column--pivot_column指定要转换的列IN(<column_list>)--column_list自定义的目标列名)

因为这里列名不允许指定为数字，真是无语。。。我重建了一个数据结构一模一样的表。

createtable#temp(Namenvarchar(10)null,Coursenvarchar(10)null,Scoreintnull)insertinto#temp(Name,Course,Score)select'小李','语文','88'unionselect'小李','数学','79'unionselect'小李','英语','85'unionselect'小明','语文','79'unionselect'小明','数学','89'unionselect'小明','英语','87'unionselect'小红','语文','84'unionselect'小红','数学','76'unionselect'小红','英语','92'select*from#tempgo

selectName姓名,max(caseCoursewhen'语文'thenScoreend)语文,max(caseCoursewhen'数学'thenScoreend)数学,max(caseCoursewhen'英语'thenScoreend)英语,sum(Score)课程总分,cast(avg(Score)asdecimal(18,2))课程平均分from#tempgroupbyName

使用 Pivot 进行 行转列：

selecta.Name姓名,a.语文,a.数学,a.英语from#temppivot(max(Score)--指定作为转换的列的值的列名forCourse--指定要转换的列的列名in(语文,数学,英语)--自定义的目标列名，即要转换列的不同的值作为列)

selecta.Name姓名,a.语文,a.数学,a.英语,b.SumScore课程总分,b.AvgScore课程平均分from#temppivot(max(Score)--指定作为转换的列的值的列名forCourse--指定要转换的列的列名in(语文,数学,英语)--自定义的目标列名，即要转换列的不同的值作为列)a,(selectt.Name,sum(t.Score)SumScore,cast(avg(t.Score)asdecimal(18,2))AvgScorefrom#temptgroupbyt.Name)bwherea.Name=b.Name

UnPivot 语法：

table_source--表名称，即数据源UNPIVOT(value_column--value_column要转换为行值的列名FORpivot_column--pivot_column指定要转换为指定的列IN(<column_list>)--column_list目标列名)

createtable#temp(Namenvarchar(10)null,Chineseintnull,Mathintnull,Englishintnull)insertinto#temp(Name,Chinese,Math,English)select'小李','88','79','85'unionselect'小明','79','89','87'unionselect'小红','84','76','92'select*from#tempgo

selectt.Name姓名,t.Course课程,t.Score分数from(selectt.Name,Course='Chinese',Score=Chinesefrom#temptunionallselectt.Name,Course='Math',Score=Mathfrom#temptunionallselectt.Name,Course='English',Score=Englishfrom#tempt)torderbyt.Name,t.Course

selectt.Name姓名,t.Course课程,t.Score分数from(selectt.Name,'Chinese'Course,ChineseScorefrom#temptunionallselectt.Name,'Math',Mathfrom#temptunionallselectt.Name,'English',Englishfrom#tempt)torderbyt.Name,t.Course

使用 UnPivot 进行 列转行：

selectt.Name姓名,t.Course课程,t.Score分数from#tempunpivot(ScoreforCoursein(Chinese,Math,English))