用PIVOT和UNPIVOT实现行列转置

（微软官方解释）FROM - Using PIVOT and UNPIVOT

https://msdn.microsoft.com/en-us/library/ms177410.aspx

 

1、PIVOT列值转置为行

列名为已知值（静态列）：

create table table1
(
  fcompany nvarchar(20)
  ,fweek nvarchar(20) 
  ,fvalue int
)

insert into table1(fcompany,fweek,fvalue)
select 'c1', 'w1',1 union all
select 'c1', 'w2',2 union all
select 'c1', 'w3',3 union all
select 'c1', 'w4',4 union all
select 'c1', 'w5',5 union all
select 'c1', 'w6',6 union all
select 'c1', 'w7',7 

insert into table1(fcompany,fweek,fvalue)
select 'c1', 'w1',2 union all
select 'c1', 'w2',1 union all
select 'c1', 'w3',0 union all
select 'c1', 'w4',1 union all
select 'c1', 'w5',2 union all
select 'c1', 'w6',3 union all
select 'c1', 'w7',4 

insert into table1(fcompany,fweek,fvalue)
select 'c2', 'w1',7 union all
select 'c2', 'w2',6 union all
select 'c2', 'w3',5 union all
select 'c2', 'w4',4 union all
select 'c2', 'w5',3 union all
select 'c2', 'w6',2 union all
select 'c2', 'w7',1 

select fcompany,w1,w2,w3,w4,w5,w6,w7
from table1 pivot (sum(fvalue) for fweek in (w1,w2,w3,w4,w5,w6,w7)) a

 

列名为未知值（动态列）：

  DECLARE @SQL NVARCHAR(MAX)
  DECLARE @SQL1 NVARCHAR(MAX)
  DECLARE @SQL2 NVARCHAR(MAX)

  --SELECT 后面的列，null转为0
  SELECT @SQL1=ISNULL(@SQL1+',','')+'ISNULL(['+AB+'],0) AS '+QUOTENAME(AB) FROM #TEMP_AB01 GROUP BY AB ORDER BY AB          
  --IN 后面的列，要转置为行的列值
  select @SQL2=ISNULL(@SQL2+',','')+QUOTENAME(AB) FROM #TEMP_AB01 GROUP BY AB ORDER BY AB    

  SET @SQL=N'SELECT A.COMPANY AS [公司],A.[CREATEDATE] AS [创建日期],A.DOCTYPE [交易性质],'
    + @SQL1 
    + N' ,TTL_AB AS [异常汇总],TTL_NORMAL AS [正常汇总],TTL_ALL AS [单据总计] '
    + ' FROM #TEMP_AB01 PIVOT (SUM(DOC_NUM) FOR AB IN ('+ @SQL2 +')) A '
    + '  ,#TEMP_AB02 B '
    + ' WHERE A.COMPANY=B.COMPANY AND A.CREATEDATE=B.CREATEDATE AND A.DOCTYPE=B.DOCTYPE '
    + ' ORDER BY 1,2,3 '

  EXEC(@SQL)

 

2、UNPIVOT 行转置为列

--1日~31日转换为行显示
select left(YearMonth,4)+'-'+right(YearMonth,2)+'-'+right(dt,2) dt,b.hcode,c.name 
from dbo.Schedule unpivot (hcode for dt in (D01,D02,D03,D04,D05,D06,D07,D08,D09,D10,D11,  
                D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31 
  )) b,dbo.HolidayCode c  
WHERE b.hcode=c.hcode 
  and EmpID='1001'
  and Left(YearMonth,4)='2017'
  and Right(YearMonth,2)='01'
order by 1,2 

 

转载于:https://www.cnblogs.com/liangchua/p/6603620.html