本文介绍了Oracle SQL 数据透视查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在表格中有数据，如下所示:
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.
这篇关于Oracle SQL 数据透视查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！