在实际应用中,经常会碰到要求将行转换成列的形式来显示查询结果。
现举例说明,
有如图A所示的表stat,表的内容为各个班级拥有水果的数量,要求将表stat的查询结果以图B的形式显示。
class fruit amount
-----------------------------
class a apple 30
class a pear 15
class b apple 40
class b pear 20
图 A
class apple_amount pear_amount
----------------------------
class a 30 15
class b 40 20
图 B
实现SQL如下:
select class, sum(apple_amount) apple_amount, sum(pear_amount) pear_amount
from (
select class, decode(fruit, 'apple', amount, 0) apple_amount, decode(fruit, 'pear', amount, 0) pear_amount
from stat
)
group by class
建表脚本:
create table stat(class varchar2(30), fruit varchar2(30), amount number(10));
insert into stat(class, fruit, amount)
values('class a', 'apple', 30);
insert into stat(class, fruit, amount)
values('class a', 'pear', 15);
insert into stat(class, fruit, amount)
values('class b', 'apple', 40);
insert into stat(class, fruit, amount)
values('class b', 'pear', 20);
commit;