如何使用Unpivot列转行_数据传输服务(DTS)-阿里云帮助中心

本文主要介绍在Oracle数据库中如何将数据表的列转换为行。

背景说明

在Oracle中使用UNPIVOT实现列转行。语法如下：

SELECT ...    
FROM  ...    
UNPIVOT [INCLUDE|EXCLUDE NULLS]    
   (unpivot_clause    
    unpivot_for_clause    
    unpivot_in_clause )    
WHERE ...

解决方案

在Oracle中使用UNPIVOT语法进行列转行的代码示例如下：

SELECT *    
FROM  pivoted_data    
UNPIVOT (    
deptsal                      
FOR saldesc                  
IN (d10_sal, d20_sal, d30_sal, d40_sal)    
);    
    
     
    
JOB       SALDESC       DEPTSAL    
---------- ---------- ----------    
CLERK     D10_SAL          1430    
CLERK     D20_SAL          2090    
CLERK     D30_SAL          1045    
SALESMAN  D30_SAL          6160    
PRESIDENT D10_SAL          5500    
MANAGER   D10_SAL          2695    
MANAGER   D20_SAL        3272.5    
MANAGER   D30_SAL          3135    
ANALYST   D20_SAL          6600

其他操作

在PolarDB O引擎中使用Crosstab函数接口进行列转行操作。

代码示例

with a as (  -- A对应原始数据（即需要列转行的数据）  
select     
  js->>'seller' as seller,       
  js->>'se_year' as se_year,      
  jan  ,    
  feb  ,    
  mar  ,    
  apr  ,    
  may  ,    
  jun  ,    
  jul  ,    
  aug  ,    
  sep  ,    
  oct  ,    
  nov  ,    
  dec     
from crosstab(    
  -- 这个是需要进行行列变换的数据源。    
  -- 排序字段为group by字段，最后一个字段为转换后的内容字段，导数第二个字段为行列变换的字段（内容为枚举，比如月份）    
  -- （必须在下一个参数中提取出对应的所有枚举值）    
  $$select jsonb_build_object('seller', seller, 'se_year', se_year) as js, se_month, sum(se_amount) from tbl_sellers_info group by 1,2 order by 1$$,        
  -- 行列转换的行，有哪些值被提取出来作为列。 这个在这里代表的是月份，也就是se_month的值     
  -- 或(select * from (values('jan'),...('dec')) t(se_month))    
  'select distinct se_month from tbl_sellers_info order by 1'          
)     
as   -- crosstab 输出格式    
(  js jsonb,  -- 第一个参数SQL内对应的order by对应的字段（1个或多个）    
   Jan numeric,  -- 第一个参数SQL内对应导数第二个字段的枚举值，（行转列）    
   feb numeric,  -- ...同上    
   mar numeric,    
   apr numeric,    
   may numeric,    
   jun numeric,    
   jul numeric,    
   aug numeric,    
   sep numeric,    
   oct numeric,    
   nov numeric,    
   dec numeric    
)     
order by 1,2    
)    
,     
-- b ， 用jsonb把多列合并为一列，并使用jsonb_each展开。  
b as (select seller, se_year, jsonb_each(row_to_json(a)::jsonb-'seller'::text-'se_year'::text) as rec  from a)     
select seller, se_year, (b.rec).key as month, (b.rec).value as sum from b;