SQL 用PIVOT和UNPIVOT 实现行列转置

SQL技巧：使用PIVOT和UNPIVOT进行行列转换

最新推荐文章于 2024-07-26 16:53:46 发布

原创最新推荐文章于 2024-07-26 16:53:46 发布 · 627 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

SQL 专栏收录该内容

51 篇文章

订阅专栏

这篇博客介绍了如何利用SQL的PIVOT和UNPIVOT操作来实现数据的行列转换。首先，通过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