在Oracle SQL中创建透视查询
在Oracle SQL中,透视查询用于将数据的垂直方向转换为水平方向。一个常见的场景是将按行组织的数据转换为按列呈现的格式。
示例
考虑一个包含列“MONTH”和“VALUE”的数据表:
MONTH | VALUE |
---|---|
1 | 100 |
2 | 200 |
3 | 300 |
4 | 400 |
5 | 500 |
6 | 600 |
预期结果
预期结果是一个透视表,其中包含每个唯一月份的列,显示相应的“VALUE”:
MONTH_JAN | MONTH_FEB | MONTH_MAR | MONTH_APR | MONTH_MAY | MONTH_JUN |
---|---|---|---|---|---|
100 | 200 | 300 | 400 | 500 | 600 |
Oracle 11g及以上版本
在Oracle 11g及更高版本中,可以使用PIVOT运算符:
<code class="language-sql">create table tq84_pivot ( month number, value number ); insert into tq84_pivot values(1, 100); insert into tq84_pivot values(2, 200); insert into tq84_pivot values(3, 300); insert into tq84_pivot values(4, 400); insert into tq84_pivot values(5, 500); insert into tq84_pivot values(6, 600); -- 同一月有多个值 insert into tq84_pivot values(1, 400); insert into tq84_pivot values(2, 350); insert into tq84_pivot values(4, 150); select * from tq84_pivot pivot ( sum (value) as sum_value for (month) in (1 as month_jan, 2 as month_feb, 3 as month_mar, 4 as month_apr, 5 as month_mai, 6 as month_jun, 7 as month_jul, 8 as month_aug, 9 as month_sep, 10 as month_oct, 11 as month_nov, 12 as month_dec) );</code>
此查询将创建所需的透视表。
以上是如何在 Oracle SQL 中将数据从行转换为列?的详细内容。更多信息请关注PHP中文网其他相关文章!