用PIVOT和UNPIVOT实现行列转置

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

weixin_30650859

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

阅读量253

点赞数

CC 4.0 BY-SA版权

文章标签： java

原文链接：http://www.cnblogs.com/liangchua/p/6603620.html

本文介绍了SQL中使用PIVOT和UNPIVOT进行数据转置的方法。包括如何将列值转置为行（PIVOT），适用于列名已知（静态列）和未知（动态列）的情况；以及如何将行转置为列（UNPIVOT）。通过实例演示了这两种操作的具体应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

（微软官方解释）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