Oracle对表中数据行列转换

如:有如下一个表 (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

 

实现方法:

procedure row_list_transition(
  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;

 

以上方法存在的问题:

1:如果max列过多,就会出现"分类(sort)关键字过长"的错误信息,可能是因为max列太多的原因.

解决方案:

用数组,把要转成列的数据放到数组中,然后对数组进行循环成列.

如:

v_field pkg_s1_soa_commons.t_string_array; --定义一个数组

 

select distinct --把要转成列的数据放到数组中
  caption_field
bulk collect into
  v_field       
from

Table1;

 

begin
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;