PIVOT在ORACLE中的语法

在Oracle数据库中，Pivot操作也称为Unpivoting或Transpose，其语法略有不同，因为Oracle SQL没有直接的PIVOT关键字。你可以使用CASE语句或者UNION ALL结合ROW_NUMBER()来达到类似的效果。这里是一个基本的例子：

SELECT *
FROM (
    SELECT mon, qty
    FROM your_table
) t
PIVOT (
    SUM(qty)
    FOR mon IN ('Jan' AS 'Jan',
                'Feb' AS 'Feb',
                ...,
                'Dec' AS 'Dec')
);

在Oracle中，你会这样做：

SELECT mon, CASE WHEN ROWNUM = 1 THEN 'Jan' ELSE NULL END AS Month,
       CASE WHEN ROWNUM = 2 THEN SUM(qty) ELSE NULL END AS Qty_Jan,
       ...
       CASE WHEN ROWNUM = 13 THEN SUM(qty) ELSE NULL END AS Qty_Dec
FROM (
    SELECT mon, qty, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
    FROM your_table
) subquery
WHERE rn BETWEEN 1 AND 13
GROUP BY mon;

这段代码首先创建了一个虚拟行号rn，然后根据月份分组并计算每个月的总计。每个月份对应一行，如果有缺失的月份，则相应列的值为NULL。