Oracle UNPIVOT 函數可以將多個列轉換為多行，從而使得數據更容易處理和分析

1. 基本用法：

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'));

2. 使用 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'));

3. 使用聚合函數對結果進行分組：

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;

4. 使用多個 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'));

5. 使用 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'));

6. 使用子查詢和多個 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 語句和子句，可以實現更高效、更靈活的數據處理。