列转行的问题

create or replace procedure ptest as
  strname varchar2(30);
  str varchar2(1000);
  strid varchar2(10);
  cursor cur is select * from userid;
begin
  str:='';
  open cur;
  loop
  fetch cur into strid,strname;
  exit when cur%notfound;
  str:=str||strname||',';
  end loop;
  str:=substr(str,1,length(str)-1);
  dbms_output.put_line(str);
end;

运行时：
SQL817> set serverout on
SQL817> exec ptest;
王五,小六,天明PL/SQL 过程已成功完成。SQL817>

没钱的日子..：
有没有办法实现向oracle系统函数那样的定义，就象substr那样，只要传递几个参数就可以实现正列传换？
因为我要在查询语句中用，所以要多条数据显示。

没钱的日子..：
有没有办法实现向oracle系统函数那样的定义，就象substr那样，只要传递几个参数就可以实现正列传换？
因为我要在查询语句中用，所以要多条数据显示。自已写一个function，思路和没钱的日子一样地

少量数据行列转换
ORARichard(没钱的日子......) 说的很好，更具体的，看看：
[Q]如何实现行列转换
[A]1、固定列数的行列转换
如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文数学英语
student1 80 70 60
student2 90 80 100
……
语句如下：
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student2、不定列行列转换
如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可
大量数据行列转换
机型统计
create table  temp_devices as
select trunc(t.oper_time) time,
       substr(t.device_name,1,instr(t.device_name,'(')-1) as dev,
       count(t.num) as numcount
from view_mobil_from_user t
where trunc(t.oper_time)>= to_date('20041201','yyyymmdd') and
      trunc(t.oper_time)<= to_date('20041228','yyyymmdd') and
      t.fld_area_id = 5
group by trunc(t.oper_time),substr(t.device_name,1,instr(t.device_name,'(')-1)select t.dev 设备名称,
       t1.numcount 日期1,
t2.numcount 日期2,
t3.numcount 日期3,
t4.numcount 日期4,
t5.numcount 日期5,
t6.numcount 日期6,
t7.numcount 日期7,
t8.numcount 日期8,
t9.numcount 日期9,
t10.numcount 日期10,
t11.numcount 日期11,
t12.numcount 日期12,
t13.numcount 日期13,
t14.numcount 日期14,
t15.numcount 日期15,
t16.numcount 日期16,
t17.numcount 日期17,
t18.numcount 日期18,
t19.numcount 日期19,
t20.numcount 日期20,
t21.numcount 日期21,
t22.numcount 日期22,
t23.numcount 日期23,
t24.numcount 日期24,
t25.numcount 日期25,
t26.numcount 日期26,
t27.numcount 日期27,
t28.numcount 日期28
from
(select distinct(dev) dev from temp_devices ) t,
(select * from temp_devices t where t.time = to_date('20041201','yyyymmdd')) t1,
(select * from temp_devices t where t.time = to_date('20041202','yyyymmdd')) t2,
(select * from temp_devices t where t.time = to_date('20041203','yyyymmdd')) t3,
(select * from temp_devices t where t.time = to_date('20041204','yyyymmdd')) t4,
(select * from temp_devices t where t.time = to_date('20041205','yyyymmdd')) t5,
(select * from temp_devices t where t.time = to_date('20041206','yyyymmdd')) t6,
(select * from temp_devices t where t.time = to_date('20041207','yyyymmdd')) t7,
(select * from temp_devices t where t.time = to_date('20041208','yyyymmdd')) t8,
(select * from temp_devices t where t.time = to_date('20041209','yyyymmdd')) t9,
(select * from temp_devices t where t.time = to_date('20041210','yyyymmdd')) t10,
(select * from temp_devices t where t.time = to_date('20041211','yyyymmdd')) t11,
(select * from temp_devices t where t.time = to_date('20041212','yyyymmdd')) t12,
(select * from temp_devices t where t.time = to_date('20041213','yyyymmdd')) t13,
(select * from temp_devices t where t.time = to_date('20041214','yyyymmdd')) t14,
(select * from temp_devices t where t.time = to_date('20041215','yyyymmdd')) t15,
(select * from temp_devices t where t.time = to_date('20041216','yyyymmdd')) t16,
(select * from temp_devices t where t.time = to_date('20041217','yyyymmdd')) t17,
(select * from temp_devices t where t.time = to_date('20041218','yyyymmdd')) t18,
(select * from temp_devices t where t.time = to_date('20041219','yyyymmdd')) t19,
(select * from temp_devices t where t.time = to_date('20041220','yyyymmdd')) t20,
(select * from temp_devices t where t.time = to_date('20041221','yyyymmdd')) t21,
(select * from temp_devices t where t.time = to_date('20041222','yyyymmdd')) t22,
(select * from temp_devices t where t.time = to_date('20041223','yyyymmdd')) t23,
(select * from temp_devices t where t.time = to_date('20041224','yyyymmdd')) t24,
(select * from temp_devices t where t.time = to_date('20041225','yyyymmdd')) t25,
(select * from temp_devices t where t.time = to_date('20041226','yyyymmdd')) t26,
(select * from temp_devices t where t.time = to_date('20041227','yyyymmdd')) t27,
(select * from temp_devices t where t.time = to_date('20041228','yyyymmdd')) t28
where t.dev=t1.dev(+)   and
t.dev=t2.dev(+)   and
t.dev=t3.dev(+)   and
t.dev=t4.dev(+)   and
t.dev=t5.dev(+)   and
t.dev=t6.dev(+)   and
t.dev=t7.dev(+)   and
t.dev=t8.dev(+)   and
t.dev=t9.dev(+)   and
t.dev=t10.dev(+)   and
t.dev=t11.dev(+)   and
t.dev=t12.dev(+)   and
t.dev=t13.dev(+)   and
t.dev=t14.dev(+)   and
t.dev=t15.dev(+)   and
t.dev=t16.dev(+)   and
t.dev=t17.dev(+)   and
t.dev=t18.dev(+)   and
t.dev=t19.dev(+)   and
t.dev=t20.dev(+)   and
t.dev=t21.dev(+)   and
t.dev=t22.dev(+)   and
t.dev=t23.dev(+)   and
t.dev=t24.dev(+)   and
t.dev=t25.dev(+)   and
t.dev=t26.dev(+)   and
t.dev=t27.dev(+)   and
t.dev=t28.dev(+)
1、默认值
2、截取字段       cun_col
3、需截取的表     cut_table
4、关键字
5、生成表最大列数
6、基础表选择条件A自动增加     条件S_where 自增标量名称S_increase 开始值 S_begin，结束值 S_end，步长 S_step
                 B特定结果集
7、生成结果存储表名




1、生成表
2、生成连接条件
3、生成列

create or replace procedure sp_tab_change(cut_table   in varchar2,--需要截取的表
                                          cut_col     in varchar2,--用来截取的分类字段
                                          cut_col_z   in varchar2,--截取字段
                                          cut_col_na  in varchar2,--分类字段别名
                                          col_name    in varchar2,--列字段别名
                                          def_value   in varchar2,--默认值
                                          S_where     in varchar2,--子表在截取表中选择数据的条件
                                          S_increase  in varchar2,--自增变量名
                                          S_begin     in varchar2,--开始数字
                                          S_end       in varchar2,--结束数值
                                          --S_step      in varchar2,--步长
                                          v_str       out varchar2) --生成的语句
as   --这个存储过程生成的sql语句还有点问题，需要删除最后的and

v_sql_t     varchar2(10000);
v_tmp_tab   varchar2(5000);
v_tmp_whe   varchar2(5000);
v_tmp_wher   varchar2(5000);
v_tmp_col   varchar2(5000);
c_tmp_col   number(10);
c_tmp       number(10);begin
  c_tmp :=S_begin;
  v_tmp_tab :=' ';
  v_tmp_col :=' ';
  v_tmp_whe :='  ';
  v_tmp_wher:='  ';
  for v_temp in S_begin..S_end loop
    v_tmp_whe :=replace(S_where,S_increase,c_tmp);
    v_tmp_tab := v_tmp_tab||' (select * from  '||cut_table||'  where '||v_tmp_whe||')  t'||c_tmp||',';
    v_tmp_col:= v_tmp_col||',nvl(t'||c_tmp||'.'||cut_col_z||','||def_value||') '||col_name||c_tmp;
    v_tmp_wher:= v_tmp_wher||'  t.'||cut_col||'=t'||c_tmp||'.'||cut_col||'(+)  and';
    c_tmp := c_tmp+1;
  end loop;
  v_sql_t := ' select t.'||cut_col||'  '||cut_col_na||v_tmp_col||'   '||
           ' from  '||v_tmp_tab||'(select distinct('||cut_col||') '||cut_col||' from  '||cut_table||') t   '||
           ' where  '||v_tmp_wher;
  v_str:=v_sql_t;
end sp_tab_change;-- Create table
create table TEMP_DEVICES
(
  TIME     DATE,
  DEV      VARCHAR2(60),
  NUMCOUNT NUMBER
)
tablespace ETS_USER
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 1M
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

条件:表A
     select * from a;
查询结果: id    name  cc
          1    王五  b
          2    小六  b
          3    天明  c
实现这样的结果就可以了:
   select cc,列行转换函数(group) from a group by cc;
查询结果: cc   列行转换函数(group)
           b    王五,小六
           c    天明

按照dakunlinux(小黑vs大亮) 和ORARichard(没钱的日子......) 的指导用下面的函数可以实现结果,
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;谢谢两位了!!
不过这样实现必须在函数中固定死分组语句,
不是很通用,如果要向系统函数sum那样,自动变化该怎么做?

调试易

列转行的问题

解决方案 »