SQL 用PIVOT和UNPIVOT 实现 行列转置

用PIVOT跟UNPIVOT实现行列转置
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 
+ ' ,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