ORACLE UNPIVOT函数

语法

 

 

   函数用途： UNPIVOT 函数用来将列值转换为行值.

• 1. INCLUDE | EXCLUDE NULLS 子句参数可以控制在结果集中是否保留值为NULL的行(新生成的行，即PIVOT_FOR_CLAUSE参数值）。当省略这个控制参数时，默认为EXCLUDE NULLS,即去除空值行。

• 2. 列转行后结果集中生成的新列一般为度量值。

• 3. PIVOT_FOR_CLAUSE参数用来定义列转行后新的行所在的列名。比如，将FEDERER,NADAL,NOVAK三个字段（列）转为行后，列名为LEGEND，其对应PIVOT_FOR_CLAUSE参数为 FOR LEGEND(或 FOR "LEGEND").

• 4. unpivot_in_clause参数用来定义将要进行列转行的所有列名，这些列名在结果集中最终将变为参数PIVOT_FOR_CLAUSE的行值。可选的AS子句即将转为行的列值重命名。

   由于unpivot函数在列转行操作中将列值转换成了一个字段的所有值（即生成一个新列），因此，所有这些列值的数据类型必须保持一致。否则会报ORA-01790:表达示必须具有与对应表达式相同的数据类型。

   示例：
   原表数据(https://blog.csdn.net/RogerFedererGO/article/details/131915274?spm=1001.2014.3001.5501)：

CREATE TABLE pivottable AS
SELECT * FROM
(SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM orders)
PIVOT
(SUM(ordertotal) FOR order_mode IN ('direct' AS Store, 'online' AS Internet));
SELECT * FROM pivot_table ORDER BY year;

 

   (1) EXclude nulls:

SELECT * FROM pivot_table
 UNPIVOT (yearly_total FOR order_mode IN (store AS 'direct',
 internet AS 'online'))
 ORDER BY year, order_mode;

 

  

 

(2) INclude nulls:

SELECT * FROM pivot_table
 UNPIVOT INCLUDE NULLS 
 (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
 ORDER BY year, order_mode;

 

参考文档 ORALCE官网 SQL Language Reference：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/About-SQL-Functions.html#GUID-D51AB228-518C-4213-8BD4-F919623D105E