行转列:
SQL> create table lc (id int,val int,valuetype int);表已创建。
SQL> insert into lc values (1,1,1);已创建 1 行。SQL> insert into lc values (1,2,1);已创建 1 行。SQL> insert into lc values (1,3,1);已创建 1 行。SQL> insert into lc values (1,4,2);已创建 1 行。SQL> insert into lc values (1,5,2);已创建 1 行。SQL> insert into lc values (1,6,2);已创建 1 行。SQL> insert into lc values (1,6,3);已创建 1 行。SQL> insert into lc values (2,6,1);已创建 1 行。SQL> commit;提交完成。SQL> select * from lc; ID VAL VALUETYPE
---------- ---------- ----------
1 1 1
1 2 1
1 3 1
1 4 2
1 5 2
1 6 2
1 6 3
2 6 1已选择8行。SQL> select id,sum(decode(valuetype,1,val,null)) "type 1",
2 sum(decode(valuetype,2,val,null)) "type 2",
3 sum(decode(valuetype,3,val,null)) "type 3"
4 from lc group by id; ID type 1 type 2 type 3
---------- ---------- ---------- ----------
1 6 15 6
2 6SQL>
SQL> create table lc (id int,val int,valuetype int);表已创建。
SQL> insert into lc values (1,1,1);已创建 1 行。SQL> insert into lc values (1,2,1);已创建 1 行。SQL> insert into lc values (1,3,1);已创建 1 行。SQL> insert into lc values (1,4,2);已创建 1 行。SQL> insert into lc values (1,5,2);已创建 1 行。SQL> insert into lc values (1,6,2);已创建 1 行。SQL> insert into lc values (1,6,3);已创建 1 行。SQL> insert into lc values (2,6,1);已创建 1 行。SQL> commit;提交完成。SQL> select * from lc; ID VAL VALUETYPE
---------- ---------- ----------
1 1 1
1 2 1
1 3 1
1 4 2
1 5 2
1 6 2
1 6 3
2 6 1已选择8行。SQL> select id,sum(decode(valuetype,1,val,null)) "type 1",
2 sum(decode(valuetype,2,val,null)) "type 2",
3 sum(decode(valuetype,3,val,null)) "type 3"
4 from lc group by id; ID type 1 type 2 type 3
---------- ---------- ---------- ----------
1 6 15 6
2 6SQL>
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货