ORACLE UNPIVOT函数
语法
![](https://img2023.cnblogs.com/blog/2755468/202307/2755468-20230726131334656-481171115.png)
函数用途: 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;
![](https://img2023.cnblogs.com/blog/2755468/202307/2755468-20230726131334626-848949181.png)
(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;
![](https://img2023.cnblogs.com/blog/2755468/202307/2755468-20230726131334636-495155388.png)
(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;
![](https://img2023.cnblogs.com/blog/2755468/202307/2755468-20230726131334707-1891139461.png)
参考文档 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