SQL行列转换[老生常谈]–SQL内置函数PIVOT 简介


试验环境:
windows server 2008
SQL server 2008 R2
行列转换函数PIVOT UNPIVOT


一、PIVOT函数简介

此函数可以将列中的内容转换为“列名”
语法列表:

table
PIVOT(
聚合函数(value)
FOR pivot_column
IN()
)


二、UNPIVOT函数简介

此函数可以将列值转换为行内容

table
UNPIVOT(
value
FOR pivot_column
IN()
)


三、函数使用举例说明


    

/*数据:行内容转换为列名*/
create table #t
(compname varchar(20),
cheXi varchar(30),
dayInfo int,
daySaleValue int)


insert into #t values('一汽丰田','锐志','1',20)
insert into #t values('一汽丰田','皇冠','1',10)
insert into #t values('一汽丰田','霸道','2',30)
insert into #t values('一汽丰田','锐志','3',40)
insert into #t values('一汽丰田','RAV4','4',60)
insert into #t values('一汽丰田','锐志','5',8)
insert into #t values('一汽丰田','霸道','6',6)
insert into #t values('一汽丰田','RAV4','5',9)
insert into #t values('一汽丰田','RAV4','10',10)

 
   
   
 select * from 
 (select compname,daySaleValue,dayInfo,chexi from  #t) as d
 /*注意事项: pivot所涉及的聚合列 value_column  和 pivot_column 
  都必须存在 上面的查询表中
 */
 pivot(sum(daySaleValue) for dayInfo 
   in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) 
   t ;
 
 
 

truncate table #t
drop table #t







/*数据:行内容转换为列名*/
create table #t
(compname varchar(20),
cheXi varchar(30),
[1] char(10),[2] char(10),[3] char(10),[4] char(10),[5] char(10),[6] char(10),
daySaleValue int)


insert into #t values('一汽丰田','锐志','1','2','3','4','5','6',20)
insert into #t values('一汽丰田','皇冠','4','5','3','3','5','6',10)
insert into #t values('一汽丰田','霸道','1','20','3','4','5','6',30)
insert into #t values('一汽丰田','锐志','1','8','3','4','5','6',40)
insert into #t values('一汽丰田','RAV4','1','9','3','4','5','6',60)
insert into #t values('一汽丰田','锐志','1','2','3','4','5','6',8)
insert into #t values('一汽丰田','霸道','1','4','3','4','5','6',6)
insert into #t values('一汽丰田','RAV4','1','2','3','4','5','6',9)
insert into #t values('一汽丰田','RAV4','1','2','3','4','5','6',10)

 
   
   
 select * from 
 (select  * from  #t) as d
 unpivot( [新列名对应value] for [新列名] 
   in([1],[2],[3],[4],[5],[6])) 
   t ;
 
 

truncate table #t
drop table #t