Oracle SQL数据透视查询 [英] Oracle SQL pivot query

问题描述

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

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之前，您必须将CASE语句替换为Oracle特定的DECODE.

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.

这篇关于Oracle SQL数据透视查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！