oracle 不定行转列 问题 菜鸟急救

Question

   id      name   number  in_class open_user    open_date 56 流量表 3 1 测试 2010-12-24 22:31:56
57 three 5 2 测试 2010-12-24 22:32:03
65 心电监样3 0 测试 2010-12-26 10:24:42
66 流量表 4 0 测试 2010-12-26 10:24:50
67 three 5 0 测试 2010-12-26 10:24:57
现在想要的效果就是按天来进行转换，根据name的不重复值这一列生成 name1,name2,name3.....number1,number2,number3..... in_class是固定的3个
   name1,name2,name3    number1,number2,number3 in_class1,in_class2,in_class3 open_user  open_date
                                                                                          12-24
                                                                                          12-26

Answer 1

http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html
这个帖子的过程

Answer 2

-----try it good luck
select 
trunc(open_date),
max(decode(rn,1,name)) name1,
max(decode(rn,1,name)) name2,
max(decode(rn,1,name)) name3, 
max(decode(rn,1,number)) number1,
max(decode(rn,1,number)) number2,
max(decode(rn,1,number)) number3 
max(decode(rn,1,in_class)) in_class1,
max(decode(rn,1,in_class)) in_class2,
max(decode(rn,1,in_class)) in_class3,
max(decode(rn,1,open_user)) open_user,
from 
(
select *,row_number()over(partition by trunc(oper_date) ordre by 1) rn from youtable
) where rn<=3 group by trunc(oper_date)

Answer 3

那你就用上面的链接里的存储过程吧
的确，11g已经支持了行转列了，明天研究下。。
http://www.cnblogs.com/tracy/archive/2010/04/21/1717407.html

Answer 4

-----呵呵。。还有点问题，序号没改
select  
trunc(open_date),
max(decode(rn,1,name)) name1,
max(decode(rn,2,name)) name2,
max(decode(rn,3,name)) name3,  
max(decode(rn,1,number)) number1,
max(decode(rn,2,number)) number2,
max(decode(rn,3,number)) number3  
max(decode(rn,1,in_class)) in_class1,
max(decode(rn,2,in_class)) in_class2,
max(decode(rn,3,in_class)) in_class3,
max(open_user) open_user,
from  
(
select *,row_number()over(partition by trunc(oper_date) ordre by 1) rn from youtable
) where rn<=3 group by trunc(oper_date)

Answer 5

你想生成多少列？oracle表最大就1000列！
500个user就达到限制！！

Answer 6

scott@ORCL> ed
已写入 file afiedt.buf  1  create or replace procedure p_line_col(
  2                                        g_col varchar2,v_numbers varchar2,
  3                                        v_in_class varchar2,cur out sys_refcursor)
  4  as
  5  str1 varchar2(2000);
  6  str2 varchar2(1000);
  7  str3 varchar2(1000);
  8  str varchar2(4000);
  9  n number:=0;
 10  begin
 11  for i in(select distinct name c_name from tt2 ) loop
 12  n:=n+1;
 13  str1:=str1||','||'max(decode(name,'''||i.c_name||''',name)) name'||n;
 14  str2:=str2||','||'max(decode(name,'''||i.c_name||''','||v_numbers||')) numbers'||n;
 15  str3:=str3||','||'max(decode(name,'''||i.c_name||''','||v_in_class||')) in_class'||n;
 16  end loop;
 17  str:='select open_user,trunc('||g_col||') '||g_col||str1||str2||str3||'
 18  from tt2 group by trunc('||g_col||'),open_user';
 19  open cur for str;
 20* end;
scott@ORCL> /过程已创建。
scott@ORCL> var cur refcursor
scott@ORCL> exec p_line_col('open_date','numbers','in_class',:cur)PL/SQL 过程已成功完成。scott@ORCL> print curOPEN OPEN_DATE      NAME1    NAME2    NAME3      NUMBERS1   NUMBERS2   NUMBERS3  IN_CLASS1  IN_CLASS2  IN
---- -------------- -------- -------- -------- ---------- ---------- ---------- ---------- ---------
测试 24-12月-10     three             流量表            5                     3          2                     1
测试 26-12月-10     three    心电监样 流量表            5          3          4          0          0          0
--代码
 create or replace procedure p_line_col(
                                       g_col varchar2,v_numbers varchar2,
                                       v_in_class varchar2,cur out sys_refcursor)
 as
 str1 varchar2(2000);
 str2 varchar2(1000);
 str3 varchar2(1000);
 str varchar2(4000);
 n number:=0;
 begin
 for i in(select distinct name c_name from tt2 ) loop
 n:=n+1;
 str1:=str1||','||'max(decode(name,'''||i.c_name||''',name)) name'||n;
 str2:=str2||','||'max(decode(name,'''||i.c_name||''','||v_numbers||')) numbers'||n;
 str3:=str3||','||'max(decode(name,'''||i.c_name||''','||v_in_class||')) in_class'||n;
 end loop;
 str:='select open_user,trunc('||g_col||') '||g_col||str1||str2||str3||'
 from tt2 group by trunc('||g_col||'),open_user';
 open cur for str;
 end;

Answer 7

如果name 超过3个不同的值  是不是还要声明变量

Answer 8

--不需要
--这个以视图来弄
scott@ORCL> ed
已写入 file afiedt.buf  1  create or replace procedure p_line_col(
  2                                         g_col varchar2,v_numbers varchar2,
  3                                         v_in_class varchar2)
  4   as
  5   str1 varchar2(2000);
  6   str2 varchar2(1000);
  7   str3 varchar2(1000);
  8   str varchar2(4000);
  9   n number:=0;
 10   begin
 11   for i in(select distinct name c_name from tt2 ) loop
 12   n:=n+1;
 13   str1:=str1||','||'max(decode(name,'''||i.c_name||''',name)) name'||n;
 14   str2:=str2||','||'max(decode(name,'''||i.c_name||''','||v_numbers||')) numbers'||n;
 15   str3:=str3||','||'max(decode(name,'''||i.c_name||''','||v_in_class||')) in_class'||n;
 16   end loop;
 17   str:='select open_user,trunc('||g_col||') '||g_col||str1||str2||str3||'
 18   from tt2 group by trunc('||g_col||'),open_user';
 19   execute immediate 'create or replace view v_tab as '||str;
 20   ---以视图的方式来
 21*  end;
scott@ORCL> /过程已创建。scott@ORCL>  exec p_line_col('open_date','numbers','in_class')PL/SQL 过程已成功完成。scott@ORCL> select * from v_tab
  2  /OPEN OPEN_DATE      NAME1    NAME2    NAME3      NUMBERS1   NUMBERS2   NUMBERS3  IN_CLASS1  IN_CLASS2  IN
---- -------------- -------- -------- -------- ---------- ---------- ---------- ---------- ---------
测试 24-12月-10     three             流量表            5                     3          2                     1
测试 26-12月-10     three    心电监样 流量表            5          3          4          0          0          0scott@ORCL> 

Answer 9

我在pl/sql 中执行 scott@ORCL>  exec p_line_col('open_date','numbers','in_class')有错误  10g支持这个写法吗

Answer 10

scott@ORCL> exec p_line_col('open_date','numbers','in_class') 你不会直接就复制吧 scott@ORCL> exec p_line_col('open_date','numbers','in_class')--这样
exec p_line_col('open_date','numbers','in_class')--过程代码，以临时表来做 推荐
 create or replace procedure p_line_col(
                                        g_col varchar2,v_numbers varchar2,
                                        v_in_class varchar2)
  as
  str1 varchar2(2000);
  str2 varchar2(1000);
  str3 varchar2(1000);
  str varchar2(4000);
  n number:=0;
  cnt number;
  begin
  for i in(select distinct name c_name from tt2 ) loop
  n:=n+1;
  str1:=str1||','||'max(decode(name,'''||i.c_name||''',name)) name'||n;
  str2:=str2||','||'max(decode(name,'''||i.c_name||''','||v_numbers||')) numbers'||n;
  str3:=str3||','||'max(decode(name,'''||i.c_name||''','||v_in_class||')) in_class'||n;
  end loop;
  str:='select open_user,trunc('||g_col||') '||g_col||str1||str2||str3||'
  from tt2 group by trunc('||g_col||'),open_user';
  select count(*) into cnt from all_tables where table_name='TT_TMP';
  if cnt>0 then
  execute immediate 'truncate table tt_tmp';
  execute immediate 'drop table tt_tmp';
  else
  execute immediate 'create global temporary table tt_tmp on commit preserve rows as '||str;
  ---以临时表
  end if;
  end;--执行过程
exec p_line_col('open_date','numbers','in_class')
--查看
 select * from tt_tmp
  2  /OPEN OPEN_DATE      NAME1    NAME2    NAME3      NUMBERS1   NUMBERS2   NUMBERS3  IN_CLASS1  IN_CLASS2  IN
---- -------------- -------- -------- -------- ---------- ---------- ---------- ---------- ---------
测试 24-12月-10     three             流量表            5                     3          2                     1
测试 26-12月-10     three    心电监样 流量表            5          3          4          0          0          0

Answer 11

exec p_line_col('open_date','numbers','in_class')我执行这个报错  invalid sql statement 

Answer 12

你就按现在这个数据 写个测试吧IID_ID          Instrument_Name Instrument_Number    operating_time     In_Jijie
        55 血压计 2 0 董川 2010-12-24 22:31:48 冬季
56 流量表 3 1 董川 2010-12-24 22:31:56 冬季
57 three 5 2 董川 2010-12-24 22:32:03 冬季
58 four 1 0 董川 2010-12-23 22:32:12 冬季
59 心电监样2 1 董川 2010-12-23 22:32:20 冬季
60 six 6 2 董川 2010-12-23 22:32:28 冬季
65 心电监样3 0 董川 2010-12-26 10:24:42 冬季
66 流量表 4 0 董川 2010-12-26 10:24:50 冬季
67 three 5 0 董川 2010-12-26 10:24:57 冬季
68 心电监样2 1 董川 2010-12-26 10:25:09 冬季

Answer 13

operator 这个字段  董川 这列字段

Answer 14

create or replace procedure p_line_col(
                                        g_col varchar2, --分组列
                                        v_numbers varchar2,  --显示的列值numbers
                                        v_in_class varchar2 --显示的列值in_class
                                       )
as
str1 varchar2(2000);
str2 varchar2(1000);
str3 varchar2(1000);
str varchar2(4000);
n number:=0;
cnt number;
begin
for i in(select distinct Instrument_Name c_name from tt2 ) loop
n:=n+1;
str1:=str1||','||'max(decode(Instrument_Name,'''||i.c_name||''',Instrument_Name)) v_name'||n;
str2:=str2||','||'max(decode(Instrument_Name,'''||i.c_name||''','||v_numbers||')) numbers'||n;
str3:=str3||','||'max(decode(Instrument_Name,'''||i.c_name||''','||v_in_class||')) in_class'||n;
end loop;
str:='select operator,trunc('||g_col||') '||g_col||str1||str2||str3||'
from tt2 group by trunc('||g_col||'),operator';
select count(*) into cnt from all_tables where table_name='TT_TMP';
if cnt>0 then
execute immediate 'truncate table tt_tmp';
execute immediate 'drop table tt_tmp';
else
execute immediate 'create global temporary table tt_tmp on commit preserve rows as '||str;
end if;
end;
SQL>  create or replace procedure p_line_col(
  2                                          g_col varchar2,
  3                                          v_numbers varchar2,
  4                                          v_in_class varchar2)
  5    as
  6    str1 varchar2(2000);
  7    str2 varchar2(1000);
  8    str3 varchar2(1000);
  9    str varchar2(4000);
 10    n number:=0;
 11    cnt number;
 12    begin
 13    for i in(select distinct Instrument_Name c_name from tt2 ) loop
 14    n:=n+1;
 15    str1:=str1||','||'max(decode(Instrument_Name,'''||i.c_name||''',Instrument_Name)) v_name'||n;
 16    str2:=str2||','||'max(decode(Instrument_Name,'''||i.c_name||''','||v_numbers||')) numbers'||n;
 17    str3:=str3||','||'max(decode(Instrument_Name,'''||i.c_name||''','||v_in_class||')) in_class'||n;
 18    end loop;
 19    str:='select operator,trunc('||g_col||') '||g_col||str1||str2||str3||'
 20    from tt2 group by trunc('||g_col||'),operator';
 21    select count(*) into cnt from all_tables where table_name='TT_TMP';
 22    if cnt>0 then
 23    execute immediate 'truncate table tt_tmp';
 24    execute immediate 'drop table tt_tmp';
 25    elseed
 26    execute immediate 'create global temporary table tt_tmp on commit preserve rows as '||str;
 27    end if;
 28    end;
 29  /
 
Procedure created
 
 
SQL> exec p_line_col('operating_time','Instrument_Number','in_class')
 
PL/SQL procedure successfully completed
 
SQL> select * from tt_tmp
  2  /
 
OPERATOR OPERATING_TIME V_NAME1  V_NAME2  V_NAME3  V_NAME4  V_NAME5  V_NAME6    NUMBERS1   NUMBERS2   NUMBERS3   NUMBERS4   NUMBERS5   NUMBERS6  IN_CLASS1  IN_CLASS2  IN_CLASS3  IN_CLASS4  IN_CLASS5  IN_CLASS6
-------- -------------- -------- -------- -------- -------- -------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
董川     2010-12-24     three             血压计   流量表                              5                     2          3                                2                     0          1            
董川     2010-12-26     three    心电监样           流量表                              5          3                     4                                0          1                     0            
董川     2010-12-23              心电监样                    four     six                          2                                1          6                     1                                0          2
 
SQL>  select * from tt2
  2  /INSTRUME INSTRUMENT_NUMBER   IN_CLASS OPER OPERATING_TIME      IN_J
-------- ----------------- ---------- ---- ------------------- ----
血压计                   2          0 董川 2010-12-24 22:31:48 冬季
流量表                   3          1 董川 2010-12-24 22:31:56 冬季
three                    5          2 董川 2010-12-24 22:32:03 冬季
four                     1          0 董川 2010-12-23 22:32:12 冬季
心电监样                 2          1 董川 2010-12-23 22:32:20 冬季
six                      6          2 董川 2010-12-23 22:32:28 冬季
心电监样                 3          0 董川 2010-12-26 10:24:42 冬季
流量表                   4          0 董川 2010-12-26 10:24:50 冬季
three                    5          0 董川 2010-12-26 10:24:57 冬季
心电监样                 2          1 董川 2010-12-26 10:25:09 冬季
 
10 rows selected
 
SQL> 

Answer 15

--代码
create or replace procedure p_line_col(
                                        g_col varchar2, --分组列
                                        v_numbers varchar2,  --显示的列值numbers
                                        v_in_class varchar2 --显示的列值in_class
                                       )
as
str1 varchar2(2000);
str2 varchar2(1000);
str3 varchar2(1000);
str varchar2(4000);
n number:=0;
cnt number;
begin
for i in(select distinct rn c_rn from v_tt2 ) loop
n:=n+1;
str1:=str1||','||'max(decode(rn,'||i.c_rn||',Instrument_Name)) v_name'||n;
str2:=str2||','||'max(decode(rn,'||i.c_rn||','||v_numbers||')) numbers'||n;
str3:=str3||','||'max(decode(rn,'||i.c_rn||','||v_in_class||')) in_class'||n;
end loop;
str:='select operator,trunc('||g_col||') '||g_col||str1||str2||str3||'
from v_tt2 group by trunc('||g_col||'),operator';
select count(*) into cnt from all_tables where table_name='TT_TMP';
if cnt>0 then
execute immediate 'truncate table tt_tmp';
execute immediate 'drop table tt_tmp';
else
execute immediate 'create global temporary table tt_tmp on commit preserve rows as '||str;
end if;
end;scott@ORCL> select * from tt2
  2  /INSTRUME INSTRUMENT_NUMBER   IN_CLASS OPER OPERATING_TIME      IN_J
-------- ----------------- ---------- ---- ------------------- ----
血压计                   2          0 董川 2010-12-24 22:31:48 冬季
流量表                   3          1 董川 2010-12-24 22:31:56 冬季
three                    5          2 董川 2010-12-24 22:32:03 冬季
four                     1          0 董川 2010-12-23 22:32:12 冬季
心电监样                 2          1 董川 2010-12-23 22:32:20 冬季
six                      6          2 董川 2010-12-23 22:32:28 冬季
心电监样                 3          0 董川 2010-12-26 10:24:42 冬季
流量表                   4          0 董川 2010-12-26 10:24:50 冬季
three                    5          0 董川 2010-12-26 10:24:57 冬季
心电监样                 2          1 董川 2010-12-26 10:25:09 冬季已选择10行。scott@ORCL> create or replace view v_tt2
  2  as
  3  select a.instrument_name,a.instrument_number,a.in_class,a.operator,a.operating_time,a.in_jiji  4  row_number() over(partition by trunc(operating_time) order by operating_time) rn
  5  from tt2 a
  6  /视图已创建。scott@ORCL> select * from v_tt2
  2  /INSTRUME INSTRUMENT_NUMBER   IN_CLASS OPER OPERATING_TIME      IN_J         RN
-------- ----------------- ---------- ---- ------------------- ---- ----------
four                     1          0 董川 2010-12-23 22:32:12 冬季          1
心电监样                 2          1 董川 2010-12-23 22:32:20 冬季          2
six                      6          2 董川 2010-12-23 22:32:28 冬季          3
血压计                   2          0 董川 2010-12-24 22:31:48 冬季          1
流量表                   3          1 董川 2010-12-24 22:31:56 冬季          2
three                    5          2 董川 2010-12-24 22:32:03 冬季          3
心电监样                 3          0 董川 2010-12-26 10:24:42 冬季          1
流量表                   4          0 董川 2010-12-26 10:24:50 冬季          2
three                    5          0 董川 2010-12-26 10:24:57 冬季          3
心电监样                 2          1 董川 2010-12-26 10:25:09 冬季          4已选择10行。scott@ORCL> 
SQL> create or replace procedure p_line_col(
  2                                          g_col varchar2, --分组列
  3                                          v_numbers varchar2,  --显示的列值numbers
  4                                          v_in_class varchar2 --显示的列值in_class
  5                                         )
  6  as
  7  str1 varchar2(2000);
  8  str2 varchar2(1000);
  9  str3 varchar2(1000);
 10  str varchar2(4000);
 11  n number:=0;
 12  cnt number;
 13  begin
 14  for i in(select distinct rn c_rn from v_tt2 ) loop
 15  n:=n+1;
 16  str1:=str1||','||'max(decode(rn,'||i.c_rn||',Instrument_Name)) v_name'||n;
 17  str2:=str2||','||'max(decode(rn,'||i.c_rn||','||v_numbers||')) numbers'||n;
 18  str3:=str3||','||'max(decode(rn,'||i.c_rn||','||v_in_class||')) in_class'||n;
 19  end loop;
 20  str:='select operator,trunc('||g_col||') '||g_col||str1||str2||str3||'
 21  from v_tt2 group by trunc('||g_col||'),operator';
 22  select count(*) into cnt from all_tables where table_name='TT_TMP';
 23  if cnt>0 then
 24  execute immediate 'truncate table tt_tmp';
 25  execute immediate 'drop table tt_tmp';
 26  else
 27  execute immediate 'create global temporary table tt_tmp on commit preserve rows as '||str;
 28  end if;
 29  end;
 30  /
 
Procedure created
 
SQL> exec p_line_col('operating_time','Instrument_Number','in_class')
 
PL/SQL procedure successfully completed
 
SQL> select * from tt_tmp
  2  /
 
OPERATOR OPERATING_TIME V_NAME1  V_NAME2  V_NAME3  V_NAME4    NUMBERS1   NUMBERS2   NUMBERS3   NUMBERS4  IN_CLASS1  IN_CLASS2  IN_CLASS3  IN_CLASS4
-------- -------------- -------- -------- -------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
董川     2010-12-24     血压计   流量表            three             2          3                     5          0          1                     2
董川     2010-12-26     心电监样  流量表   心电监样  three             3          4          2          5          0          0          1          0
董川     2010-12-23     four     心电监样           six               1          2                     6          0          1                     2
 
SQL>