SQLServer行列转换 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

--------------------------------列转行--------------------------------------
PIVOT 
创建测试表,插入测试数据 
create table test(id int,name varchar(20),quarter int,profile int) 
insert into test values(1,'a',1,1000) 
insert into test values(1,'a',2,2000) 
insert into test values(1,'a',3,4000) 
insert into test values(1,'a',4,5000) 
insert into test values(2,'b',1,3000) 
insert into test values(2,'b',2,3500) 
insert into test values(2,'b',3,4200) 
insert into test values(2,'b',4,5500) 


select * from test 
id name quarter profile 
-------------------------------
1 a 1 1000 
1 a 2 2000 
1 a 3 4000 
1 a 4 5000 
2 b 1 3000 
2 b 2 3500 
2 b 3 4200 
2 b 4 5500 


(8 row(s) affected) 


利用PIVOT将个季度的利润转成横向显示
select id,name, 
[1] as '一季度', 
[2] as '二季度', 
[3] as '三季度', 
[4] as '四季度' 
from 
test 
pivot 
( 
sum(profile) 
for quarter in 
([1],[2],[3],[4]) 
) 
as pvt 


id name 一季度 二季度 三季度 四季度 
------------------------------------
1 a 1000 2000 4000 5000 
2 b 3000 3500 4200 5500 


(2 row(s) affected) 








--------------------------------行转列--------------------------------------
UNPIVOT 
建立测试表,插入测试数据 
drop table test 
create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int) 
insert into test values(1,'a',1000,2000,4000,5000) 
insert into test values(2,'b',3000,3500,4200,5500) 
select * from test 


id name Q1 Q2 Q3 Q4 
--------------------------------
1 a 1000 2000 4000 5000 
2 b 3000 3500 4200 5500 


(2 row(s) affected) 


利用UNPIVOT,将同一行中四个季度的列数据转换成四行数据
select id,name,quarter,profile 
from 
test 
unpivot 
( 
profile 
for quarter in 
([Q1],[Q2],[Q3],[Q4]) 
) 
as unpvt 


id name quarter profile 
-------------------------------
1 a Q1 1000 
1 a Q2 2000 
1 a Q3 4000 
1 a Q4 5000 
2 b Q1 3000 
2 b Q2 3500 
2 b Q3 4200 
2 b Q4 5500 


(8 row(s) affected) 








--------------------------------列转行--------------------------------------
DROP table #student
CREATE TABLE #student (stdname nvarchar(10),stdsubject nvarchar(10),result int)
INSERT INTO #student VALUES ('张三','语文',80)
INSERT INTO #student values ('张三','数学',90)
INSERT INTO #student VALUES ('张三','物理',85)
INSERT INTO #student VALUES ('李四','语文',85)
INSERT INTO #student values ('李四','数学',92)
INSERT INTO #student VALUES ('李四','物理',82)
INSERT INTO #student VALUES ('李四','化学',82)
INSERT INTO #student VALUES ('李四','化学',82)
SELECT * FROM #student


建立临时表,对数据进行处理(静态的sql 1)
select distinct *  into #table from #student
select * from #table
select stdname,
isnull(sum( case stdsubject when '语文' then Result end), 0 ) [语文], 
isnull(sum( case stdsubject when '数学' then Result end), 0 ) [数学],
isnull(sum( case stdsubject when '物理' then Result end), 0 ) [物理],
isnull(sum( case stdsubject when '化学' then Result end), 0 ) [化学]
from #table 
group by stdname  
drop table #table


(静态的sql 2)
select distinct * into #table from #student
select stdname,
    [语文] as "语文",
    [数学] as "数学",
    [物理] as "物理",
    [化学] as "化学" into #table2
from #table pivot(sum(result) for stdsubject in ([语文],[数学],[物理],[化学])) as pvt


select stdname,语文,isnull(数学,0) '数学',物理,isnull(化学,0) '化学' from #table2
drop table #table
drop table #table2
 
(动态sql 1)
declare @sql varchar(1000) 
select distinct * into #table from #student
set @sql = 'select stdname'
select @sql = @sql + ',isnull(sum(case stdsubject when '''+ stdsubject +''' then Result end),0) [ ' + stdsubject + ' ] '  
from (select distinct stdsubject from  #table) as rs 
select @sql = @sql + ' from #table group by stdname'  
print @sql
exec (@sql)
drop table #table


(动态sql 2),不用临时表
declare @sql varchar(1000) 
set @sql = 'select stdname'
select @sql = @sql + ',isnull(sum(distinct case stdsubject when ''' + stdsubject + ''' then Result end),0) [ ' + stdsubject + ' ] '  
from (select distinct stdsubject from #student) as rs 
select @sql = @sql + ' from #student group by stdname'  
print @sql
exec (@sql)








--------------------------------行转列--------------------------------------
DROP table #student2
CREATE TABLE #student2 (stdname nvarchar(10),化学 int,数学 int,物理 int ,语文 int )
INSERT INTO #student2 VALUES ('李四',164,92,82,85)
INSERT INTO #student2 VALUES ('张三',0,90,85,80)
SELECT * FROM #student2 




select [name] into #tmpCloumns
from tempdb.dbo.syscolumns
where id=object_id('tempdb.dbo.#student2')
and [name]<>'stdname'
select *  from #tmpCloumns 


declare @strSql nvarchar(800)
select @strSql=''
select @strSql=@strSql+'union all'+char(10)+char(13)+'select [stdname],'''+[name]+''' as [科目],['+[name]+']'+char(10)+char(13)+'from [#student2]'+char(10)+char(13)
from #tmpCloumns


select @strSql=substring(@strSql,11,len(@strSql))+'order by stdname,[科目]'
--print @strSql
exec(@strsql)