Oracle中使用decode进行数据的列转换为行的试验
Oracle中使用decode进行数据的列转换为行的试验
如何将以下数据格式的数据转换为
初始化格式如下:
1 市政府 房产 1 2 3
2 省政府 房产 4 3 4
3 肥东 房产 5 4 5
4 肥西 房产 6 5 7
5 长风 房产 7 7 8
6 淮南 房产 8 9 5
7 市政府 汽车 1 2 3
8 省政府 汽车 4 3 4
9 肥东 汽车 5 4 5
10 肥西 汽车 6 5 7
11 长风 汽车 7 7 8
12 淮南 汽车 8 9 5
13 肥西 奢侈品 6 5 7
14 长风 奢侈品 7 7 8
15 淮南 奢侈品 8 9 5
16 市政府 贵重首饰 1 2 3
17 省政府 贵重首饰 4 3 4
18 肥东 贵重首饰 5 4 5
需要转变为
房产 汽车 奢侈品 奢侈品
PRO_TYPE 场次 销售价 佣金 场次 销售价 佣金 场次 销售价 佣金 场次 销售价 佣金
1 淮南 8 9 5 8 9 5 8 9 5
2 肥东 5 4 5 5 4 5 5 4 5
3 市政府 1 2 3 1 2 3 1 2 3
4 省政府 4 3 4 4 3 4 4 3 4
5 肥西 6 5 7 6 5 7 6 5 7
6 长风 7 7 8 7 7 8 7 7 8
处理sql语句如下:
select pro_type,
sum(decode(bm_tyoe, '房产', jiage1)) 房产场次,
sum(decode(bm_tyoe, '房产', jiage2)) 房产销售价,
sum(decode(bm_tyoe, '房产', jiage3)) 房产佣金,
sum(decode(bm_tyoe, '汽车', jiage1)) 汽车场次,
sum(decode(bm_tyoe, '汽车', jiage2)) 房产销售价,
sum(decode(bm_tyoe, '汽车', jiage3)) 汽车佣金,
sum(decode(bm_tyoe, '奢侈品', jiage1)) 奢侈品场次,
sum(decode(bm_tyoe, '奢侈品', jiage2)) 奢侈品销售价,
sum(decode(bm_tyoe, '奢侈品', jiage3)) 奢侈品佣金,
sum(decode(bm_tyoe, '贵重首饰', jiage1)) 贵重首饰场次,
sum(decode(bm_tyoe, '贵重首饰', jiage2)) 贵重首饰销售价,
sum(decode(bm_tyoe, '贵重首饰', jiage3)) 贵重首饰佣金
from tmp_table
group by pro_type
Sql代码
- prompt PL/SQL Developer import file
- prompt Created on 2012年7月5日 by jiaorg
- set feedback off
- set define off
- prompt Creating TMP_TABLE...
- create table TMP_TABLE
- (
- PRO_TYPE VARCHAR2(20),
- BM_TYOE VARCHAR2(20),
- JIAGE1 NUMBER(5),
- JIAGE2 NUMBER(5),
- JIAGE3 NUMBER(5)
- )
- tablespace ABRES
- pctfree 10
- initrans 1
- maxtrans 255
- storage
- (
- initial 64K
- next 1M
- minextents 1
- maxextents unlimited
- );
- prompt Disabling triggers for TMP_TABLE...
- alter table TMP_TABLE disable all triggers;
- prompt Deleting TMP_TABLE...
- delete from TMP_TABLE;
- commit;
- prompt Loading TMP_TABLE...
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('市政府', '房产', 1, 2, 3);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('省政府', '房产', 4, 3, 4);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('肥东', '房产', 5, 4, 5);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('肥西', '房产', 6, 5, 7);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('长风', '房产', 7, 7, 8);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('淮南', '房产', 8, 9, 5);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('市政府', '汽车', 1, 2, 3);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('省政府', '汽车', 4, 3, 4);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('肥东', '汽车', 5, 4, 5);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('肥西', '汽车', 6, 5, 7);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('长风', '汽车', 7, 7, 8);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('淮南', '汽车', 8, 9, 5);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('肥西', '奢侈品', 6, 5, 7);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('长风', '奢侈品', 7, 7, 8);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('淮南', '奢侈品', 8, 9, 5);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('市政府', '贵重首饰', 1, 2, 3);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('省政府', '贵重首饰', 4, 3, 4);
- insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
- values ('肥东', '贵重首饰', 5, 4, 5);
- commit;
- prompt 18 records loaded
- prompt Enabling triggers for TMP_TABLE...
- alter table TMP_TABLE enable all triggers;
- set feedback on
- set define on
- prompt Done.
评论暂时关闭