將部門薪資總額由「列存」轉為「行存」顯示：
SELECT '工資' AS 部門,
SUM(DECODE(k.deptno, '10', k.g)) AS 部門10,
SUM(DECODE(k.deptno, '20', k.g)) AS 部門20,
SUM(DECODE(k.deptno, '30', k.g)) AS 部門30,
SUM(DECODE(k.deptno, '40', k.g)) AS 部門40
FROM (
SELECT SUM(e.sal) g, e.deptno
FROM emp e
LEFT JOIN dept d ON d.deptno = e.empno
GROUP BY e.deptno
) k;
原理： `DECODE` 充當條件加總器，配合 `SUM` 將符合條件的行聚合到對應欄位。11g+ 可改用標準 `PIVOT` 語法。
| 函數 | 語法範例 | 用途 | 關鍵字句 |
|---|---|---|---|
| 排序編號 | RANK() OVER (ORDER BY sal DESC) | 並列同名次，跳過下一名次 (1,2,2,4) | ORDER BY |
| 密集排序 | DENSE_RANK() OVER (ORDER BY sal DESC) | 並列同名次，不跳過 (1,2,2,3) | ORDER BY |
| 唯一行號 | ROW_NUMBER() OVER (ORDER BY sal DESC) | 1,2,3,4... 無並列，常用分頁/去重 | ORDER BY |
| 分組聚合 | AVG(sal) OVER (PARTITION BY deptno) | 各部門平均薪資附掛每行 | PARTITION BY |
| 滑動視窗-行 | SUM(sal) OVER (ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) | 當前行±1行薪資總和 | ROWS BETWEEN |
| 滑動視窗-值 | COUNT(*) OVER (ORDER BY sal RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING) | 薪資差距±100 內的人數 | RANGE BETWEEN |
| 首/尾值 | FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal) | 部門最低薪資 | PARTITION BY + ORDER BY |
| 前後值 | LAG(sal, 1) OVER (ORDER BY hiredate) | 上一筆薪資（依入職日排序） | OFFSET |
-- 取各部門薪資前 2 高員工
SELECT *
FROM (
SELECT e.*,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) rn
FROM emp e
) WHERE rn <= 2;
`DECODE` 行列轉換是舊版本樞紐分析利器，現代建議改用 `PIVOT`/`UNPIVOT`。窗口函數則是分析型 SQL 核心工具，掌握 `PARTITION BY`、`ORDER BY`、`ROWS/RANGE BETWEEN` 三大子句組合，即可解決 90% 的「同組比較、累計、移動平均、Top-N」需求。
