Oracle查询优化-行转列

使用case when 方式

case 条件
when 值1 then 返回值1
when 值2 then 返回值2
..........
else 默认值
end

SELECT
    id,
    sum( CASE month WHEN 'Jan' THEN amount ELSE 0 END ) AS Jan_amount,
    sum( CASE month WHEN 'Feb' THEN amount ELSE 0 END ) AS Feb_amount,
    sum( CASE month WHEN 'Mar' THEN amount ELSE 0 END ) AS Mar_amount,
    sum( CASE month WHEN 'Apr' THEN amount ELSE 0 END ) AS Apr_amount,
    sum( CASE month WHEN 'May' THEN amount ELSE 0 END ) AS May_amount,
    sum( CASE month WHEN 'Jun' THEN amount ELSE 0 END ) AS Jun_amount,
    sum( CASE month WHEN 'Jul' THEN amount ELSE 0 END ) AS Jul_amount,
    sum( CASE month WHEN 'Aug' THEN amount ELSE 0 END ) AS Aug_amount,
    sum( CASE month WHEN 'Sep' THEN amount ELSE 0 END ) AS Sep_amount,
    sum( CASE month WHEN 'Oct' THEN amount ELSE 0 END ) AS Oct_amount,
    sum( CASE month WHEN 'Nov' THEN amount ELSE 0 END ) AS Nov_amount,
    sum( CASE month WHEN 'Dec' THEN amount ELSE 0 END ) AS Dec_amount 
FROM
    REST 
GROUP BY
    id

使用 decode函数

decode函数： DECODE(条件, 值1, 返回值1, 值2,返回值2, 值3,返回值3, . . . else 缺省值)
含义：if 条件 = 值1 then 返回值1 elsif 条件 = 值2 then 返回值2 else (缺省值) endif

SELECT
    id,
    sum( decode( month, 'Jan', amount, 0 ) ) Jan_amount,
    sum( decode( month, 'Feb', amount, 0 ) ) Feb_amount,
    sum( decode( month, 'Mar', amount, 0 ) ) Mar_amount,
    sum( decode( month, 'Apr', amount, 0 ) ) Apr_amount,
    sum( decode( month, 'May', amount, 0 ) ) May_amount,
    sum( decode( month, 'Jun', amount, 0 ) ) Jun_amount,
    sum( decode( month, 'Jul', amount, 0 ) ) Jul_amount,
    sum( decode( month, 'Aug', amount, 0 ) ) Aug_amount,
    sum( decode( month, 'Sep', amount, 0 ) ) Sep_amount,
    sum( decode( month, 'Oct', amount, 0 ) ) Oct_amount,
    sum( decode( month, 'Nov', amount, 0 ) ) Nov_amount,
    sum( decode( month, 'Dec', amount, 0 ) ) Dec_amount 
FROM
    REST 
GROUP BY
    id

使用pivot函数

pivot
(
<聚合函数>(要聚合的列)
for <要转换的列> in (要转换的列值 as 要转换成的列名)
)

SELECT
    NVl(Jan_amount,0) Jan_amount,
    NVl(Feb_amount,0) Feb_amount,
    NVl(Mar_amount,0) Mar_amount,
    NVl(Apr_amount,0) Apr_amount,
    NVl(May_amount,0) May_amount,
    NVl(Jun_amount,0) Jun_amount,
    NVl(Jul_amount,0) Jul_amount,
    NVl(Aug_amount,0) Aug_amount,
    NVl(Sep_amount,0) Sep_amount,
    NVl(Oct_amount,0) Oct_amount,
    NVl(Nov_amount,0) Nov_amount,
    NVl(Dec_amount,0) Dec_amount
FROM
     REST pivot (
        SUM(amount) FOR month IN (
            'Jan' AS Jan_amount,
            'Feb' AS Feb_amount,
            'Mar' AS Mar_amount,
            'Apr' AS Apr_amount,
            'May' AS May_amount,
            'Jun' AS Jun_amount,
            'Jul' AS Jul_amount,
            'Aug' AS Aug_amount,
            'Sep' AS Sep_amount,
            'Oct' AS Oct_amount,
            'Nov' AS Nov_amount,
            'Dec' AS Dec_amount 
        ) 
    );