带有子查询的 Oracle 数据透视表

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/19298424/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 02:00:10  来源:igfitidea点击:

Oracle pivot with subquery

xmloracleplsqloracle11gpivot

提问by Dan

I'm using pivot in Oracle PL SQL Developer as follows:

我在 Oracle PL SQL Developer 中使用数据透视如下:

SELECT *
FROM population
PIVOT (AVG(Total) for Data_Type IN ('Group1','Group2','Group3'))

This works fine, but I don't want to have to edit every time a new column is added or one is changed (i.e. Group4, 5, 6 etc), so I tried a sub-query as follows:

这工作正常,但我不想每次添加新列或更改一个列(即 Group4、5、6 等)时都必须进行编辑,因此我尝试了一个子查询,如下所示:

SELECT *
FROM population
PIVOT (AVG(Total) for Data_Type IN (SELECT Data_Type FROM population))

This results in the following error: ORA-00936: missing expression.

这会导致以下错误:ORA-00936:缺少表达式。

After some research, it appears that I can generate the results with XML, so I tried the following:

经过一番研究,看来我可以用 XML 生成结果,所以我尝试了以下操作:

SELECT *
FROM population
PIVOT XML(AVG(Total) for Data_Type IN (ANY))

This actually generates the desired data, but in XML format. So my question is, how can I convert the XML results into standard table format within PL SQL Developer? Or, if I want to bring the generated XML file into a tool like Crystal Reports, I need to have a schema file for these results. Is that something that can easily be auto generated within the SQL?

这实际上会生成所需的数据,但采用 XML 格式。所以我的问题是,如何在 PL SQL Developer 中将 XML 结果转换为标准表格式?或者,如果我想将生成的 XML 文件带入像 Crystal Reports 这样的工具中,我需要有这些结果的模式文件。这是可以在 SQL 中轻松自动生成的东西吗?

回答by Przemyslaw Kruglej

Would you consider using PIPELINED function to achieve your goal?

您会考虑使用 PIPELINED 函数来实现您的目标吗?

I have written a an example of such a function. The example is based on the table, sample data and PIVOTquery from Tom Kyte's articles which you can find on his site:

我写了一个这样的函数的例子。该示例基于PIVOTTom Kyte 文章中的表、示例数据和查询,您可以在他的网站上找到这些文章:

Tom Kyte's article about PIVOT/UNPIVOT

Tom Kyte 关于 PIVOT/UNPIVOT 的文章

Tom Kyte's article about PIPELINED functions

Tom Kyte 关于 PIPELINED 函数的文章

The example works as follows.

该示例的工作方式如下。

We create two types:

我们创建两种类型:

  • t_pivot_test_obj - type which holds columns we want to retrieve from XML
  • t_pivot_test_obj_tab - nested table type of above objects.
  • t_pivot_test_obj - 保存我们想要从 XML 中检索的列的类型
  • t_pivot_test_obj_tab - 上述对象的嵌套表类型。

Then we create a PIPELINED function which contains the query with PIVOT, which generates XML (so you do not have to hard-code the values you want to pivot over). This function extracts data from generated XML and passes (PIPEs) rows to the calling query as they are generated (on the fly - they are not generated all at once which is important for performance).

然后我们创建一个 PIPELINED 函数,其中包含带有 的查询PIVOT,它生成 XML(因此您不必硬编码要转换的值)。此函数从生成的 XML 中提取数据,并在生成行时将 (PIPE) 行传递给调用查询(动态 - 它们不是一次性生成的,这对性能很重要)。

Finally, you write a query which selects records from that function (at the end is an example of such a query).

最后,您编写一个查询,该查询从该函数中选择记录(最后是此类查询的示例)。

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;

CREATE TYPE t_pivot_test_obj AS OBJECT (
  customer_id   NUMBER,
  product_code  VARCHAR2(5),
  sum_quantity  NUMBER
);
/

CREATE TYPE t_pivot_test_obj_tab IS TABLE OF t_pivot_test_obj;
/

CREATE OR REPLACE FUNCTION extract_from_xml RETURN t_pivot_test_obj_tab PIPELINED
AS
  v_xml XMLTYPE;
  v_item_xml XMLTYPE;
  v_index NUMBER;
  v_sum_quantity NUMBER;

  CURSOR c_customer_items IS
    SELECT customer_id, product_code_xml
      FROM (SELECT customer_id, product_code, quantity
              FROM pivot_test)
      PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code 
                                                                      FROM pivot_test));
BEGIN
  -- loop through all records returned by query with PIVOT
  FOR v_rec IN c_customer_items
  LOOP
    v_xml := v_rec.product_code_xml;
    v_index := 1;

    -- loop through all ITEM elements for each customer
    LOOP
      v_item_xml := v_xml.EXTRACT('/PivotSet/item[' || v_index || ']');

      EXIT WHEN v_item_xml IS NULL;

      v_index := v_index + 1;

      IF v_item_xml.EXTRACT('/item/column[@name="SUM_QUANTITY"]/text()') IS NOT NULL THEN
        v_sum_quantity := v_item_xml.EXTRACT('/item/column[@name="SUM_QUANTITY"]/text()').getNumberVal();
      ELSE
        v_sum_quantity := 0;
      END IF;

      -- finally, for each customer and item - PIPE the row to the calling query
      PIPE ROW(t_pivot_test_obj(v_rec.customer_id,
                                v_item_xml.EXTRACT('/item/column[@name="PRODUCT_CODE"]/text()').getStringVal(),
                                v_sum_quantity));
    END LOOP;
  END LOOP;
END;
/

SELECT customer_id, product_code, sum_quantity
  FROM TABLE(extract_from_xml())
;

Output:

输出:

CUSTOMER_ID            PRODUCT_CODE SUM_QUANTITY           
---------------------- ------------ ---------------------- 
1                      A            10                     
1                      B            20                     
1                      C            30                     
1                      D            0                      
2                      A            40                     
2                      B            0                      
2                      C            50                     
2                      D            0                      
3                      A            60                     
3                      B            70                     
3                      C            80                     
3                      D            90                     
4                      A            100                    
4                      B            0                      
4                      C            0                      
4                      D            0                      

16 rows selected

回答by durette

You can generate the text of your first SQL statement by iterating, then separately execute that statement.

您可以通过迭代生成第一个 SQL 语句的文本,然后单独执行该语句。

If you don't mind a quasi-dynamic solution, you might schedule the creation of a VIEW in this manner using dynamic SQL (i.e. EXECUTE IMMEDIATE).

如果您不介意准动态解决方案,您可以使用动态 SQL(即 EXECUTE IMMEDIATE)以这种方式安排 VIEW 的创建。

(A Crystal Report, to my knowledge, would need to know the column names in advance.)

(据我所知,Crystal Report 需要提前知道列名。)

Edited to add code. I didn't test this. Note, too, that this will break when the SQL statement exceeds 32KB, regardless of the actual number of multi-byte characters.

编辑以添加代码。我没有测试这个。还要注意,当 SQL 语句超过 32KB 时,无论多字节字符的实际数量如何,这都会中断。

DECLARE
   sql_statement_ VARCHAR2(32767);
BEGIN
   sql_statement_ := 'CREATE OR REPLACE VIEW population_view AS ' ||
                     'SELECT * FROM population ' ||
                     'PIVOT (AVG(total) FOR data_type IN (';
   FOR rec_ IN (SELECT DISTINCT data_type FROM population) LOOP
      sql_statement_ := sql_statement_ ||
                        '''' || REPLACE(rec_.data_type, '''', '''''') || ''', ';
   END LOOP;
   /* trim last comma and space */
   sql_statement_ = SUBSTR(1, sql_statement_, LENGTH(sql_statement_) - 2);
   /* close statement */
   sql_statement_ = sql_statement_ || ')) WITH READ ONLY';
   /* Rub your rabbit's foot, scatter garlic, and grab your four leaf clover.
      This could hurt if we didn't properly handle injection above. */
   EXECUTE IMMEDIATE sql_statement_;
END;
/