阅读量:0
Oracle UNPIVOT 函数可以将多个列转换为多行,从而使得数据更容易处理和分析
- 基本用法:
SELECT * FROM (SELECT id, value1, value2, value3 FROM table_name) UNPIVOT (value FOR column_name IN (value1 AS 'Value1', value2 AS 'Value2', value3 AS 'Value3'));
- 使用 WHERE 子句过滤数据:
SELECT * FROM (SELECT id, value1, value2, value3 FROM table_name WHERE id > 100) UNPIVOT (value FOR column_name IN (value1 AS 'Value1', value2 AS 'Value2', value3 AS 'Value3'));
- 使用聚合函数对结果进行分组:
SELECT column_name, AVG(value) as average_value FROM (SELECT id, value1, value2, value3 FROM table_name) UNPIVOT (value FOR column_name IN (value1 AS 'Value1', value2 AS 'Value2', value3 AS 'Value3')) GROUP BY column_name;
- 使用多个 UNPIVOT 操作:
SELECT * FROM (SELECT id, value1, value2, value3, value4, value5, value6 FROM table_name) UNPIVOT (value1 FOR column_name1 IN (value1 AS 'Value1', value2 AS 'Value2', value3 AS 'Value3')) UNPIVOT (value2 FOR column_name2 IN (value4 AS 'Value4', value5 AS 'Value5', value6 AS 'Value6'));
- 使用 JOIN 连接多个表:
SELECT * FROM (SELECT t1.id, t1.value1, t1.value2, t2.value3, t2.value4 FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id) UNPIVOT ((value1, value2) FOR column_name IN ((t1.value1, t1.value2) AS 'Table1', (t2.value3, t2.value4) AS 'Table2'));
- 使用子查询和多个 UNPIVOT 操作:
SELECT * FROM (SELECT id, value1, value2, value3, value4, value5, value6 FROM table_name) UNPIVOT (value1 FOR column_name1 IN (value1 AS 'Value1', value2 AS 'Value2', value3 AS 'Value3')) UNPIVOT (value2 FOR column_name2 IN (value4 AS 'Value4', value5 AS 'Value5', value6 AS 'Value6')) WHERE column_name1 <> column_name2;
这些高级用法展示了如何在实际应用中利用 Oracle UNPIVOT 函数来处理复杂的数据转换需求。通过组合不同的 SQL 语句和子句,可以实现更高效、更灵活的数据处理。