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

本文介绍了带有子查询的 Oracle 数据透视表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！

问题描述

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

SELECT *来自人口PIVOT (AVG(Total) for Data_Type IN ('Group1','Group2','Group3'))

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

SELECT *来自人口PIVOT (AVG(Total) for Data_Type IN (SELECT Data_Type FROM population))

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

经过一番研究，似乎可以用 XML 生成结果，因此我尝试了以下操作:

SELECT *来自人口PIVOT XML(AVG(Total) for Data_Type IN (ANY))

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

解决方案

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

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

Tom Kyte 关于 PIVOT/UNPIVOT 的文章

Tom Kyte 关于 PIPELINED 函数的文章

该示例的工作原理如下.

我们创建两种类型:

t_pivot_test_obj - 包含我们想要从 XML 中检索的列的类型
t_pivot_test_obj_tab - 上述对象的嵌套表类型.

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

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

CREATE TABLE pivot_test (身份证号码，customer_id NUMBER，产品代码 VARCHAR2(5),数量 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);犯罪;创建类型 t_pivot_test_obj 作为对象(customer_id NUMBER，产品代码 VARCHAR2(5),sum_quantity NUMBER);/创建类型 t_pivot_test_obj_tab 是 t_pivot_test_obj 表；/创建或替换功能 extract_from_xml 返回 t_pivot_test_obj_tab 流水线作为v_xml XMLTYPE;v_item_xml XMLTYPE;v_index NUMBER;v_sum_quantity NUMBER;光标 c_customer_items IS选择 customer_id，product_code_xmlFROM (SELECT customer_id, product_code, 数量来自pivot_test)PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code)从pivot_test));开始-- 使用 PIVOT 遍历查询返回的所有记录FOR v_rec IN c_customer_items环形v_xml := v_rec.product_code_xml;v_index := 1;-- 遍历每个客户的所有 ITEM 元素环形v_item_xml := v_xml.EXTRACT('/PivotSet/item[' || v_index || ']');当 v_item_xml 为空时退出；v_index := v_index + 1;如果 v_item_xml.EXTRACT('/item/column[@name="SUM_QUANTITY"]/text()') 不是 NULL 那么v_sum_quantity := v_item_xml.EXTRACT('/item/column[@name="SUM_QUANTITY"]/text()').getNumberVal();别的v_sum_quantity := 0;万一;-- 最后，对于每个客户和项目 - 将行通过管道传输到调用查询管道行(t_pivot_test_obj(v_rec.customer_id，v_item_xml.EXTRACT('/item/column[@name="PRODUCT_CODE"]/text()').getStringVal(),v_sum_quantity));结束循环；结束循环；结尾;/选择 customer_id、product_code、sum_quantity从表(extract_from_xml());

输出:

CUSTOMER_ID PRODUCT_CODE SUM_QUANTITY-------------- -------------- ----------------------1 101 乙 201 厘米 301 D 02 一个 402 乙 02 C 502 D 03 一个 603 乙 703 C 803D 904 1004 乙 04 C 04 D 0已选择 16 行

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

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:

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.

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

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?

解决方案

Would you consider using PIPELINED function to achieve your goal?

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

Tom Kyte's article about PIVOT/UNPIVOT

Tom Kyte's article about PIPELINED functions

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.

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).

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

这篇关于带有子查询的 Oracle 数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！

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

问题描述

相关文档推荐