Oracle在执行时间之前枢转未知数量的列

问题描述

我有这样的东西:

id  cod
1   a
1   b
1   c
2   d
2   e
3   f
3   g

我需要这样的东西:

id  cod 1   cod 2   cod 3
1   a       b       c
2   d       e   
3   f       g   

您了解，没有办法知道在执行之前oracle必须生成多少列.

you understand that there is no way to know how many column oracle will have to generate before the execution time.

推荐答案

您可以使用下面的代码p_pivot过程.它根据您的表动态构建视图v_test.然后，您可以从此视图中进行选择，如下所示:

You can use procedure p_pivot, code below. It dynamically builds view v_test based on your table.Then you can select from this view like here:

Connected to Oracle Database 10g Release 10.2.0.4.0 

SQL> execute p_pivot;

PL/SQL procedure successfully completed

SQL> select * from v_test;

        ID COD1  COD2  COD3
---------- ----- ----- -----
         1 a     b     c
         2 d     e     
         3 f     g     

过程(请将表名从test更改为代码中的表名):

Procedure (please change table name from test to your table name in code):

create or replace procedure p_pivot is
  v_cols number;
  v_sql varchar2(4000);
begin
  select max(cnt) into v_cols
    from (select count(1) cnt from test group by id);

  v_sql := 
  'create or replace view v_test as 
  with t as (select row_number() over (partition by id order by cod) rn, test.* from test)
  select id';

  for i in 1..v_cols
  loop
    v_sql := v_sql || ', max(decode(rn, '||i||', cod)) cod'||i;
  end loop;
  v_sql := v_sql || ' from t group by id';
  execute immediate v_sql;
end p_pivot;

这篇关于Oracle在执行时间之前枢转未知数量的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！