行列转换两例的思考

1、多行转成一列

(1)、建测试表及插入测试数据

create table t(i int,a varchar2(1));
insert into t(i,a)
select 1,'a' from dual
union all
select 1,'b' from dual
union all
select 1,'d' from dual
union all
select 1,'e' from dual
union all
select 2,'z' from dual
union all
select 2,'t' from dual
union all
select 2,'x' from dual
union all
select 3,'a' from dual;
commit;

(2)、实现多列转一行的自定义函数

create or replace function fn_coltorow(n number) return varchar2 istype typ_cursor is ref cursor;v_cursor  typ_cursor;v_col_val varchar2(10);v_result  varchar2(4000) := '';v_sql     varchar2(200);
beginv_sql := 'select a from t where i = ' || n;open v_cursor for v_sql;loopfetch v_cursorinto v_col_val;exit when v_cursor%notfound;v_result := v_result || ',' || v_col_val;end loop;return substr(v_result, 2);
end;

(3)、运行自定义函数查看

select i,fn_coltorow(i) from t group by i;

(4)、使用系统函数查看(wmsys.wm_concat(a))

select i,wmsys.wm_concat(a) from t group by i;

总结：运行自定义函数与系统函数是一样的功效。

2、大连银行项目时的一个行列转换简化

select customerid,accountmonth,'0042',
max(decode(displayno,1,item2value,0)) 房地产经营收入,
max(decode(displayno,2,item2value,0)) 营业成本,
max(decode(displayno,3,item2value,0)) 销售费用,
max(decode(displayno,4,item2value,0))  营业税金及附加,
max(decode(displayno,6,item2value,0)) 房地产经营利润,
max(decode(displayno,7,item2value,0)) 其它业务利润,
max(decode(displayno,8,item2value,0)) 管理费用,
max(decode(displayno,9,item2value,0))  财务费用,
max(decode(displayno,11,item2value,0)) 经营利润,
max(decode(displayno,12,item2value,0)) 投资收益,
max(decode(displayno,13,item2value,0))  期货收益,
max(decode(displayno,14,item2value,0)) 营业外收入,
max(decode(displayno,15,item2value,0)) 营业外支出,
max(decode(displayno,16,item2value,0)) 以前年度损益调整
from finance_data where reportno='0042' group by customerid,accountmonth;

总结：当初做的时候，又是用变量，又是用游标，花了一周时间，程序非常繁琐；现在看了下，一个查询语句一个小时不要就搞定；现在想想，那时候水平真是太次了。

3、对于分组有限的情况，也可以如下来实现：

1、建表及初始化数据语句

create table EDW_MAP_ASSET_STATUS
(SRC_NO        VARCHAR2(4) not null,SRC_CODE_DESC VARCHAR2(100)
);insert into edw_map_asset_status (SRC_NO, SRC_CODE_DESC) values ('1005', '正常');
insert into edw_map_asset_status (SRC_NO, SRC_CODE_DESC) values ('1005', '已抵押/质押,未生效');
insert into edw_map_asset_status (SRC_NO, SRC_CODE_DESC) values ('1005', '已抵押/质押,已生效');
insert into edw_map_asset_status (SRC_NO, SRC_CODE_DESC) values ('1005', '其他机构抵押/质押');
insert into edw_map_asset_status (SRC_NO, SRC_CODE_DESC) values ('1005', '注销失效');
insert into edw_map_asset_status (SRC_NO, SRC_CODE_DESC) values ('1005', '到期失效');
insert into edw_map_asset_status (SRC_NO, SRC_CODE_DESC) values ('1006', '正常');
insert into edw_map_asset_status (SRC_NO, SRC_CODE_DESC) values ('1006', '未生效');
insert into edw_map_asset_status (SRC_NO, SRC_CODE_DESC) values ('1006', '抵押中');
insert into edw_map_asset_status (SRC_NO, SRC_CODE_DESC) values ('1006', '注销');
insert into edw_map_asset_status (SRC_NO, SRC_CODE_DESC) values ('1006', '核销');
commit;

2、实现多行转为一行的SQL语句

select src_no,max(decode(group_rank, 1, '1、' || src_code_desc)) || '  ' ||max(decode(group_rank, 2, '2、' || src_code_desc)) || '  ' ||max(decode(group_rank, 3, '3、' || src_code_desc)) || '  ' ||max(decode(group_rank, 4, '4、' || src_code_desc)) || '  ' ||max(decode(group_rank, 5, '5、' || src_code_desc)) || '  ' ||max(decode(group_rank, 6, '6、' || src_code_desc)) || '  ' || ' 'from (select a.src_no,a.src_code_desc,row_number() over(partition by a.src_no order by a.src_no) group_rankfrom edw_map_asset_status a)group by src_no;select * from edw_map_asset_status;

3、说明：此方法对于分类情况有限的情形，如果分组情况不可控，则不能用此方法实现。