如:有如下一个表 (Table1)
ps_pi_id, caption_field, caption_field_value
c1 a 1
c1 b 2
c1 c 4
......
c1 k x1
c2 a 5
c2 b 2
c2 c 2
......
c2 k x2
......
cn k xn
要转换成如下格式:
no a b c ........k --字段名 把"caption_field"转成字段名
c1 1 2 4 .......x1
c2 5 2 2 .......x2
......
cn x1 x2 x3.....xn
ps_pi_id, caption_field, caption_field_value
c1 a 1
c1 b 2
c1 c 4
......
c1 k x1
c2 a 5
c2 b 2
c2 c 2
......
c2 k x2
......
cn k xn
要转换成如下格式:
no a b c ........k --字段名 把"caption_field"转成字段名
c1 1 2 4 .......x1
c2 5 2 2 .......x2
......
cn x1 x2 x3.....xn
实现方法:
procedure row_list_transition(
a_change_data out t_cursor
) is
cursor c_cursor is
select distinct
caption_field
from
Table1;
a_change_data out t_cursor
) is
cursor c_cursor is
select distinct
caption_field
from
Table1;
v_field varchar2(32767);
v_sql varchar2(32767);
begin
v_sql := 'select ps_pi_id';
open c_cursor;
loop fetch c_cursor into v_field;
exit when csr%notfound;
v_sql:= v_sql|| ', sum(decode(caption_field,' || ''' || v_field || ''' ||
', caption_field_value,0))' || v_field;
end loop;
v_sql := v_sql || '
from
Table1 group by ps_pi_id';
open a_change_data for v_sql;
end row_list_transition;
如果caption_field_value 为字符串型:
把字符串转换成ASCII码,进行求和,再转回来. 但ascii只对字符,所以当字符串多位时,只取第一位的 .
用max就可以对字符串进行分类了.
把
sum(decode(mf.caption_field,' || ''' || v_field || ''' ||
', mf.caption_field_value,0))' || tmp_km;
改成:
max(decode(mf.caption_field, ''' || v_field || ''', mf.caption_field_value, null)) ' || v_field;
把
sum(decode(mf.caption_field,' || ''' || v_field || ''' ||
', mf.caption_field_value,0))' || tmp_km;
改成:
max(decode(mf.caption_field, ''' || v_field || ''', mf.caption_field_value, null)) ' || v_field;
以上方法存在的问题:
1:如果max列过多,就会出现"分类(sort)关键字过长"的错误信息,可能是因为max列太多的原因.
解决方案:
用数组,把要转成列的数据放到数组中,然后对数组进行循环成列.
如:
v_field pkg_s1_soa_commons.t_string_array; --定义一个数组
select distinct --把要转成列的数据放到数组中
caption_field
bulk collect into
v_field
from
caption_field
bulk collect into
v_field
from
Table1;
begin
select distinct
pi.pi_work_no,
pi.pi_name';
select distinct
pi.pi_work_no,
pi.pi_name';
if v_field.count > 0 then --把 数组元素进行循环成列.
for v_i in v_field.first..v_field.last loop
v_sql := v_sql || '
, pkg_s1_m9_spec_manage.get_person_spec(psi.psi_pi_id, ''' || v_field(v_i) || ''') ' || v_field(v_i);
end loop ;
end if;
v_sql := v_sql || '
from Table1;
open a_change_data for v_sql;
end row_list_transition;