时间戳

首页 > 解决方案 > oracle 动态数据透视错误：SQL 命令未正确结束

sql - oracle 动态数据透视错误：SQL 命令未正确结束

问题描述

我有一个表，其数据是根据所需的输出与枢轴获得的。但我想动态创建列数。

我的桌子：

create table myTable(ROW_NAME varchar(10),COLUMN_NAME varchar(10),COLUMN_NAME_VALUE varchar(10));

表数据：

insert into myTable (ROW_NAME,COLUMN_NAME,COLUMN_NAME_VALUE)
select 'ROW1','COL1','R1C1' from dual
union all select 'ROW1','COL2','R1C2' from dual
union all select 'ROW1','COL3','R1C3' from dual
union all select 'ROW2','COL1','R2C1' from dual
union all select 'ROW2','COL2','R2C2' from dual
union all select 'ROW2','COL3','R2C3' from dual
union all select 'ROW3','COL1','R3C1' from dual
union all select 'ROW3','COL2','R3C3' from dual
union all select 'ROW3','COL3','R3C3' from dual

我的查询：

select * from myTable
  pivot (
    max (COLUMN_NAME_VALUE)
    for COLUMN_NAME
    in (
       'COL1' as COL1,'COL2' as COL2,'COL3' as COL3
    )
  )
ORDER BY ROW_NAME;

上面的查询有效，但我想动态获取列。

我的动态查询：

DECLARE
    mycols VARCHAR2(1000);
   sqlCommand varchar2(1000);
   TYPE PivotCurTyp IS REF CURSOR;
   pivot_cv   PivotCurTyp;
   piv_rec  mytable%ROWTYPE;
BEGIN
    select (select LISTAGG(COLUMN_NAME, ',') from myTable group by ROW_NAME FETCH FIRST 1 ROWS ONLY) into mycols from dual;
    select Concat('select * from myTable pivot ( max (COLUMN_NAME_VALUE) for COLUMN_NAME in (',Concat(mycols,')) ORDER BY ROW_NAME;')) into sqlCommand from dual;
    
 
    OPEN pivot_cv FOR sqlCommand;
    LOOP
     FETCH pivot_cv INTO piv_rec;
     EXIT WHEN pivot_cv%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('ROW_NAME: ' || piv_rec.ROW_NAME || ' COL1: ' ||
         piv_rec.COLUMN_NAME_VALUE || 'COL2: ' || piv_rec.COLUMN_NAME_VALUE || 'COL3: ' || piv_rec.COLUMN_NAME_VALUE);
   END LOOP;
   CLOSE pivot_cv;

END;
/

注意：上述查询的等价物可以在 SQL Server 上生成，我已经创建了它。

db<>fiddle中的演示

谢谢你的帮助

标签： sqloracleplsql

解决方案

您的脚本中有 3 个问题：

  • 以分号结尾的动态查询（这是“SQL 命令未正确结束”的原因）
  • 子句中的标识符in而不是字符串文字（您可以使用'foo'或'foo' as foo但不能foo单独使用）
  • 不正确piv_rec的类型 - 在数据透视之后使用表格格式，而不是在数据透视之前

概括：

DECLARE
   mycols VARCHAR2(1000);
   sqlCommand varchar2(1000);
   TYPE PivotCurTyp IS REF CURSOR;
   pivot_cv   PivotCurTyp;
   type pivotted is record (row_name myTable.row_name%type, col1 myTable.column_name_value%type, col2 myTable.column_name_value%type, col3 myTable.column_name_value%type);
   piv_rec  pivotted;
BEGIN
    select (select LISTAGG('''' || COLUMN_NAME || '''', ',') from myTable group by ROW_NAME FETCH FIRST 1 ROWS ONLY) into mycols from dual;
    select Concat('select * from myTable pivot ( max (COLUMN_NAME_VALUE) for COLUMN_NAME in (',Concat(mycols,')) ORDER BY ROW_NAME')) into sqlCommand from dual;
  DBMS_OUTPUT.PUT_LINE(sqlCommand);
   OPEN pivot_cv FOR sqlCommand;
   LOOP
     FETCH pivot_cv INTO piv_rec;
     EXIT WHEN pivot_cv%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('ROW_NAME: ' || piv_rec.ROW_NAME || ' COL1: ' ||
         piv_rec.COL1 || ' COL2: ' || piv_rec.COL2 || ' COL3: ' || piv_rec.COL3);
   END LOOP;
   CLOSE pivot_cv;
END;
/

更新了 db fiddle （顺便说一句，作曲 fiddle 很有帮助）

推荐阅读