废话不多说,先看行转列效果图,列转行就是图片顺序反过来
1、行转列
创建一个表
create table A_BANK
(
NAME VARCHAR2(100),
BANK VARCHAR2(50),
MONEY NUMBER
)
插入几条数据
insert into A_BANK values ('jack','icbc',8800);
insert into A_BANK values ('jack','boc',9900);
insert into A_BANK values ('lucy','icbc',10);
insert into A_BANK values ('lucy','boc',69000);
insert into A_BANK values ('jack','abc',7700);
insert into A_BANK values ('lucy','abc',4);
结果如 表1 所示
NAME | BANK | MONEY |
---|---|---|
jack | icbc | 8800 |
jack | boc | 9900 |
lucy | icbc | 10 |
lucy | boc | 6900 |
jack | abc | 7700 |
lucy | abc | 4 |
通过pivot函数转换成以下格式
select t.*,
(t.icbc + t.boc + t.abc) as total
from (select *
from A_BANK pivot ( max(money) for bank in ('icbc' as icbc , 'boc' as boc, 'abc' as abc) )
) t;
结果如 表2 所示
bank | icbc | boc | abc | total |
---|---|---|---|---|
jack | 8800 | 9900 | 7700 | 26400 |
lucy | 10 | 69000 | 4 | 69014 |
2、列转行
也可以通过unpivot函数回转成表1格式,首先我们根据上面的结果创建另外一张表
create table B_BANK as select t.*,
(t.icbc + t.boc + t.abc) as total
from (select *
from A_BANK pivot ( max(money) for bank in ('icbc' as icbc , 'boc' as boc, 'abc' as abc) )
) t;
然后对新表进行列转行操作
select name,bank,money from B_BANK unpivot (money for bank in (icbc, boc, abc));
结果如 表3 所示
NAME | BANK | MONEY |
---|---|---|
jack | icbc | 8800 |
jack | boc | 9900 |
jack | abc | 7700 |
lucy | icbc | 10 |
lucy | boc | 6900 |
lucy | abc | 4 |