mssql sqlserver sql脚本行列互转方法分享

 
摘要:
下文主要讲述行列转换和列行转换,
采用两种方式讲述行列转换:
1 case when
2 pivot和unpivot系统内置函数


例 行列转换 举例说明

   
CREATE TABLE A
(keyId int identity,---自动流水号
 nian int not null, ---年份
 info1 varchar(30),
 info2 varchar(30),
 qty int            ---数量
 )
 
 insert into A (nian,info1,info2,qty)values(2012,'A','B',2)
 insert into A (nian,info1,info2,qty)values(2013,'B','C',3)
 insert into A (nian,info1,info2,qty)values(2008,'C','D',4)
 insert into A (nian,info1,info2,qty)values(2018,'D','E',5)
 insert into A (nian,info1,info2,qty)values(2012,'A','F',6)
 insert into A (nian,info1,info2,qty)values(2008,'B','G',7)
 insert into A (nian,info1,info2,qty)values(2012,'C','H',12)
 insert into A (nian,info1,info2,qty)values(2013,'D','I',16)
 insert into A (nian,info1,info2,qty)values(2013,'A','J',20)
 go
--行转列方法1:CASE WHEN,兼容sql2000
select info1,
sum(case when nian=2012 then qty end) as [2012],
sum(case when nian=2008 then qty end) as [2008],
sum(case when nian=2018 then qty end) as [2018],
sum(case when nian=2013 then qty end) as [2013]
from A
group by info1;
GO
--行转列方法2:PIVOT,sql2005及以后版本
select *
from (select info1,nian,qty from A) as infotest
pivot(sum(qty) for nian in([2012],[2008],[2018],[2013]))as lie
GO

truncate table A
drop table A 
 


  CREATE TABLE A
( info1 varchar(30),
  [2012] int,
  [2008] int,
  [2018] int,
  [2013] int
 )
 go
 
 insert into A values('A',8,null,null,20)
 insert into A values('B',null,7,null,3)
 insert into A values('C',12,4,null,null)
 insert into A values('D',null,null,5,16)
 go
 
 ---列行转换 适用于sql server 2005及以上版本
 SELECT info1,nian,qty
from A
unpivot(qty for nian in([2012],[2008],[2018],[2013]))as test
GO
   
truncate table A
drop table A