如何將以下數(shù)據(jù)格式的數(shù)據(jù)轉(zhuǎn)換為
初始化格式如下:
1 市政府 房產(chǎn) 1 2 3
2 省政府 房產(chǎn) 4 3 4-
3 肥東 房產(chǎn) 5 4 5
4 肥西 房產(chǎn) 6 5 7
5 長風(fēng) 房產(chǎn) 7 7 8
6 淮南 房產(chǎn) 8 9 5
7 市政府 汽車 1 2 3
8 省政府 汽車 4 3 4
9 肥東 汽車 5 4 5
10 肥西 汽車 6 5 7
11 長風(fēng) 汽車 7 7 8
12 淮南 汽車 8 9 5
13 肥西 奢侈品 6 5 7
14 長風(fēng) 奢侈品 7 7 8
15 淮南 奢侈品 8 9 5
16 市政府 貴重首飾 1 2 3
17 省政府 貴重首飾 4 3 4
18 肥東 貴重首飾 5 4 5
需要轉(zhuǎn)變?yōu)?/p>
房產(chǎn) 汽車 奢侈品 奢侈品
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 長風(fēng) 7 7 8 7 7 8 7 7 8
處理sql語句如下:
select pro_type,
sum(decode(bm_tyoe, '房產(chǎn)', jiage1)) 房產(chǎn)場次,
sum(decode(bm_tyoe, '房產(chǎn)', jiage2)) 房產(chǎn)銷售價,
sum(decode(bm_tyoe, '房產(chǎn)', jiage3)) 房產(chǎn)傭金,
sum(decode(bm_tyoe, '汽車', jiage1)) 汽車場次,
sum(decode(bm_tyoe, '汽車', jiage2)) 房產(chǎn)銷售價,
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 ('市政府', '房產(chǎn)', 1, 2, 3);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('省政府', '房產(chǎn)', 4, 3, 4);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('肥東', '房產(chǎn)', 5, 4, 5);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('肥西', '房產(chǎn)', 6, 5, 7);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('長風(fēng)', '房產(chǎn)', 7, 7, 8);
insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3)
values ('淮南', '房產(chǎn)', 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 ('長風(fēng)', '汽車', 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 ('長風(fēng)', '奢侈品', 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.
本文出自:億恩科技【mszdt.com】
服務(wù)器租用/服務(wù)器托管中國五強!虛擬主機域名注冊頂級提供商!15年品質(zhì)保障!--億恩科技[ENKJ.COM]
|