在SQL中使用嵌套选择时枢轴不起作用 [英] pivot does not work when using a nested select in SQL

问题描述

我需要透视用户指定的特定表的列. 问题是从每个表到数据透视表的列数是动态的. 因此下面的代码从表中获取列的名称.

I have a requirement to pivot the columns of a particular table specified from a user. the problem is the number of columns from each table to pivot is dynamic. so the code below gets the name of the columns from the table.

SELECT DISTINCT 
                LISTAGG('''' || column_name || '''', ',') 
                    WITHIN GROUP (ORDER BY column_name) AS temp_in_statement 
                FROM (SELECT DISTINCT column_name FROM all_tab_columns WHERE table_name = 'DIM_XYZ')

上面的代码以下列格式返回列:

the code above returns the columns in the following format:

col1, col2

为此，我必须使用数据透视表，并将上面的代码插入下面的代码中，以便对列进行透视.

I have to use a pivot for this requirement and plug the code above in the code below in order to pivot the columns.

SELECT * FROM
(
  SELECT table_name, column_name
  FROM ALL_TAB_COLUMNS
  WHERE
      table_name = 'DIM_XYZ'

)
PIVOT
(
  MIN(column_name)
  FOR column_name IN ( 

-- values added manually 

 'col1','col2'

-- values added manually 


    )
)
ORDER BY table_name;

在这种情况下，代码可以正常工作，但是当用select语句替换'col1'，'col2'来检索列名时，系统会引发以下错误:

The code works fine in this case but when replacing 'col1','col2' by the select statement to retrieve columns names the system throws the following error:

ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
Error at Line: 39 Column: 40

代码:

SELECT * FROM
(
  SELECT table_name, column_name
  FROM ALL_TAB_COLUMNS
  WHERE
      table_name = 'DIM_XYZ'

)
PIVOT
(
  MIN(column_name)
  FOR column_name IN ( 

--code below does not work when plugged in the statement above
              SELECT DISTINCT 
                LISTAGG('''' || column_name || '''', ',') 
                    WITHIN GROUP (ORDER BY column_name) AS temp_in_statement 
                FROM (SELECT DISTINCT column_name FROM all_tab_columns WHERE table_name = 'DIM_XYZ')
 --code above does not work
    )
)
ORDER BY table_name;
----------------

你们知道如何解决这个问题吗?

do you guys have any idea how to solve this problem?

推荐答案

您不能直接将动态表达式添加为数据透视表的输入， 您可以尝试类似的操作，通过PL/SQL块检索变量中表的所有列，然后以某种方式将其传递给Oracle Pivot表功能.

You cannot directly add dynamic expressions as an input to Pivot table, You can try something like this where we retrieve all the columns of the table in a variable via a PL/SQL Block and then pass it in a way to the expected by the Oracle Pivot Table Functionality.

SET serveroutput ON;


    DECLARE 
    sqlquery VARCHAR(32767);
    cols VARCHAR2(32767);
    BEGIN

      SELECT listagg('''' || column_name || '''',   ',') within
      GROUP(
      ORDER BY column_name)
      INTO cols
      FROM
        (SELECT DISTINCT column_name
         FROM all_tab_columns
         WHERE TABLE_NAME = 'TABLE_NAME')
      ;

      sqlquery := '      
    SELECT * FROM
    (
      SELECT table_name, column_name
      FROM ALL_TAB_COLUMNS
      WHERE
          table_name = ''TABLE_NAME''
    )
    PIVOT
    (
      MIN(column_name)
      FOR column_name IN ( 
            ''||cols||''
        )
      )
      ORDER BY table_name';

      DBMS_OUTPUT.PUT_LINE(sqlquery);

      EXECUTE IMMEDIATE sqlquery;

    END;
    /

这篇关于在SQL中使用嵌套选择时枢轴不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！