Oracle SQL 数据透视查询

2021-12-01 00:00:00 pivot sql oracle

我在表格中有数据，如下所示:

I have data in a table as seen below:

MONTH VALUE

1     100
2     200
3     300
4     400
5     500
6     600

我想写一个 SQL 查询，结果如下:

I want to write a SQL query so that result is given as below:

MONTH_JAN MONTH_FEB MONTH_MAR MONTH_APR MONTH_MAY MONTH_JUN
100       200       300       400       500       600

推荐答案

Oracle 9i+ 支持:

SELECT SUM(CASE WHEN t.month = 1 THEN t.value ELSE 0 END) AS JAN,
       SUM(CASE WHEN t.month = 2 THEN t.value ELSE 0 END) AS FEB,
       SUM(CASE WHEN t.month = 3 THEN t.value ELSE 0 END) AS MAR,
       SUM(CASE WHEN t.month = 4 THEN t.value ELSE 0 END) AS APR,
       SUM(CASE WHEN t.month = 5 THEN t.value ELSE 0 END) AS MAY,
       SUM(CASE WHEN t.month = 6 THEN t.value ELSE 0 END) AS JUN
  FROM YOUR_TABLE t

你只列出了两列——像这样的东西可能应该按年份分组.

You only list two columns -- something like this should probably be grouped by year.

有 ANSI PIVOT(和 UNPIVOT)语法，但 Oracle 直到 11g 才支持它.在 9i 之前，您必须用 Oracle 特定的 DECODE 替换 CASE 语句.

There is ANSI PIVOT (and UNPIVOT) syntax, but Oracle didn't support it until 11g. Prior to 9i, you'd have to replace the CASE statements with Oracle specific DECODE.

相关文章