时间戳

首页 > 解决方案 > 在 Oracle 中将动态列传递给 Pivot

oracle - 在 Oracle 中将动态列传递给 Pivot

问题描述

我是 oracle 的新手，我正在尝试使用以下查询将动态生成的列名传递到 oracle 中

DECLARE
   v_cols VARCHAR2(100);
   v_query VARCHAR2(4000);

BEGIN

    SELECT LISTAGG('''' ||product_code||'''',',') WITHIN GROUP (ORDER BY product_code) 
    INTO v_cols
    FROM (
        SELECT  DISTINCT product_code
        FROM pivot_test
        );

    v_query:='
      SELECT *
      FROM   (
              SELECT product_code,
                     quantity
              FROM   pivot_test) 
              PIVOT (sum(quantity) AS qunts 
              FOR product_code IN ('|| v_cols ||'));';

    EXECUTE IMMEDIATE v_query;
    --dbms_output.Put_line(v_cols); 
    --dbms_output.Put_line(v_query);   
END;

生成的列是'A'，'B'，'C'，'D'，使用动态列生成的查询是

SELECT *
FROM   (
              SELECT 
                     product_code,
                     quantity
              FROM   pivot_test) PIVOT (sum(quantity) AS qunts FOR product_code IN ('A','B','C','D'));

结果： 结果

当我接受上述查询并单独运行它时，它运行正常，但是当我使用时EXECUTE IMMEDIATE v_query;出现错误

  ORA-00911: invalid character
  ORA-06512: at line 20

我不知道是什么导致了这里的问题，请指出这个动态查询执行有什么问题

用于测试的值

CREATE TABLE pivot_test
  (
     id           NUMBER,
     customer_id  NUMBER,
     product_code VARCHAR2(5),
     quantity     NUMBER
  );

INSERT INTO pivot_test VALUES (1,1,'A',10);    
INSERT INTO pivot_test VALUES (2,1,'B',20);    
INSERT INTO pivot_test VALUES (3,1,'C',30);    
INSERT INTO pivot_test VALUES (4,2,'A',40);    
INSERT INTO pivot_test VALUES (5,2,'C',50);    
INSERT INTO pivot_test VALUES (6,3,'A',60);
INSERT INTO pivot_test VALUES (7,3,'B',70);
INSERT INTO pivot_test VALUES (8,3,'C',80);    
INSERT INTO pivot_test VALUES (9,3,'D',90);    
INSERT INTO pivot_test VALUES (10,4,'A',100);

COMMIT;

标签： oracleoracle11gpivotdynamic-sql

解决方案

  1. 对于ORA-00911错误，您需要删除;附加到动态生成的末尾v_query
  2. 要显示输出，您可以将结果提取到游标中。整个块可以进入您的动态 SQL。示例如下：
set serveroutput on;

declare
   v_cols varchar2(100);
   v_query varchar2(4000);

begin

select listagg('''' ||product_code||'''',',') within group (order by product_code) into v_cols
from(
select  distinct product_code
 from pivot_test
);

v_query:='
declare
   cur_pivot_test   sys_refcursor;
   a    number;
   b    number;
   c    number;
   d    number;
begin
    open cur_pivot_test for
        select * from (select product_code, quantity from pivot_test) 
        pivot (sum (quantity)
        as qunts
        for product_code in ('|| v_cols ||'));

    fetch cur_pivot_Test into a, b, c, d;

    close cur_pivot_test;

    dbms_output.put_line (rpad(''A_QTY'',10) || '' | '' || rpad(''B_QTY'',10) || '' | '' || rpad(''C_QTY'',10) || '' | '' ||  rpad(''D_QTY'',10) );
    dbms_output.put_line (rpad(a,10) || '' | '' || rpad(b,10) || '' | '' || rpad(c,10) || '' | '' ||  rpad(d,10) );              

    end;
';

  execute immediate v_query;

end;


输出：

A_QTY      | B_QTY      | C_QTY      | D_QTY     
210        | 90         | 160        | 90

推荐阅读