数据行列转换,通常在做报表的时候用到。PIVOT 提供的语法比一系列复杂的 SELECT…CASE 语句中所指定的语法更简单和更具可读性。
语法解释:
table_source — 表名称,即数据源
PIVOT(
聚合函数(value_column) — value_column 要转换为 列值 的列名
FOR pivot_column — pivot_column 指定要转换的列
IN(<column_list>) — column_list 自定义的目标列名
)
演示应用,创建一个用于演示的临时表:
- create table #temp
- (
- 年份 nvarchar(10) null,
- 月份 nvarchar(10) null,
- 数量 int null
- )
- insert into #temp(年份,月份,数量)
- select '2015','1','5645' union
- select '2015','2','1234' union
- select '2015','3','7982' union
- select '2016','1','6465' union
- select '2016','2','7942' union
- select '2016','3','8453' union
- select '2017','1','4653' union
- select '2017','2','1358' union
- select '2017','3','7842'
- select * from #temp
需求一,按年份分组,不同的月份为一列。
实现方式一:
- -- 按年份分组,不同的月份为一列
- select t.年份,
- sum(case t.月份 when '1' then t.数量 end) '1月份',
- sum(case t.月份 when '2' then t.数量 end) '2月份',
- sum(case t.月份 when '3' then t.数量 end) '3月份'
- from #temp t
- group by t.年份
另外2中方式:
- -- 使用左外连接查询
- select t.年份,t1.数量 '1月份',t2.数量 '2月份',t3.数量 '3月份' from #temp t
- left join (select 年份,数量 from #temp where 月份='1') t1 on t.年份=t1.年份
- left join (select 年份,数量 from #temp where 月份='2') t2 on t.年份=t2.年份
- left join (select 年份,数量 from #temp where 月份='3') t3 on t.年份=t3.年份
- group by t.年份,t1.数量,t2.数量,t3.数量
- -- 使用自连接查询
- select t.年份,t1.数量 '1月份',t2.数量 '2月份',t3.数量 '3月份'
- from #temp t,
- (select 年份,数量 from #temp where 月份='1') t1,
- (select 年份,数量 from #temp where 月份='2') t2,
- (select 年份,数量 from #temp where 月份='3') t3
- where t.年份=t1.年份 and t.年份=t2.年份 and t.年份=t3.年份
- group by t.年份,t1.数量,t2.数量,t3.数量
- select t.年份, t.[1],t.[2],t.[3] from #temp1
- PIVOT(
- sum(数量)
- for 月份
- in([1],[2],[3])
- ) t
From MSDN:
The first character must be one of the following:
- A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.
- The underscore (_), at sign (@), or number sign (#).