oracleでdecodeを用いてデータの列を行に変換する試験
10589 ワード
次のデータフォーマットのデータを
初期化フォーマットは次のとおりです.
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 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 5 8 9
2肥東5 4 5 5 5 4 5 5 5 4
3市役所1 2 3 1 2 3 1 2 3 3 3 3 3 3 3
4省政府4 3 4 4 4 4 4 4 4 4 4 4 4 4 4
5肥西6 5 7 6 5 7 6 6 5
6長風7 7 7 8 7 7 8 7 7 8 7 8 8 8 8
sql文の処理は次のとおりです.
初期化フォーマットは次のとおりです.
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 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 5 8 9
2肥東5 4 5 5 5 4 5 5 5 4
3市役所1 2 3 1 2 3 1 2 3 3 3 3 3 3 3
4省政府4 3 4 4 4 4 4 4 4 4 4 4 4 4 4
5肥西6 5 7 6 5 7 6 6 5
6長風7 7 7 8 7 7 8 7 7 8 7 8 8 8 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))
ｆｒｏｍ tmp_table
group by pro_type 1: prompt PL/SQL Developer import file 2: prompt Created on 2012 7 5 by jiaorg 3: set feedback off 4: set define off 5: prompt Creating TMP_TABLE... 6: create table TMP_TABLE 7: ( 8: PRO_TYPE VARCHAR2(20), 9: BM_TYOE VARCHAR2(20), 10: JIAGE1 NUMBER(5), 11: JIAGE2 NUMBER(5), 12: JIAGE3 NUMBER(5) 13: ) 14: tablespace ABRES 15: pctfree 10 16: initrans 1 17: maxtrans 255 18: storage 19: ( 20: initial 64K 21: next 1M 22: minextents 1 23: maxextents unlimited 24: ); 25: 26: prompt Disabling triggers for TMP_TABLE... 27: alter table TMP_TABLE disable all triggers; 28: prompt Deleting TMP_TABLE... 29: delete from TMP_TABLE; 30: commit; 31: prompt Loading TMP_TABLE... 32: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 33: values (' ', ' ', 1, 2, 3); 34: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 35: values (' ', ' ', 4, 3, 4); 36: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 37: values (' ', ' ', 5, 4, 5); 38: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 39: values (' ', ' ', 6, 5, 7); 40: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 41: values (' ', ' ', 7, 7, 8); 42: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 43: values (' ', ' ', 8, 9, 5); 44: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 45: values (' ', ' ', 1, 2, 3); 46: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 47: values (' ', ' ', 4, 3, 4); 48: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 49: values (' ', ' ', 5, 4, 5); 50: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 51: values (' ', ' ', 6, 5, 7); 52: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 53: values (' ', ' ', 7, 7, 8); 54: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 55: values (' ', ' ', 8, 9, 5); 56: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 57: values (' ', ' ', 6, 5, 7); 58: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 59: values (' ', ' ', 7, 7, 8); 60: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 61: values (' ', ' ', 8, 9, 5); 62: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 63: values (' ', ' ', 1, 2, 3); 64: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 65: values (' ', ' ', 4, 3, 4); 66: insert into TMP_TABLE (PRO_TYPE, BM_TYOE, JIAGE1, JIAGE2, JIAGE3) 67: values (' ', ' ', 5, 4, 5); 68: commit; 69: prompt 18 records loaded 70: prompt Enabling triggers for TMP_TABLE... 71: alter table TMP_TABLE enable all triggers; 72: set feedback on 73: set define on 74: prompt Done.
blog:http://blog.jiaoronggui.cn