1. <i id='1zch8'><tr id='1zch8'><dt id='1zch8'><q id='1zch8'><span id='1zch8'><b id='1zch8'><form id='1zch8'><ins id='1zch8'></ins><ul id='1zch8'></ul><sub id='1zch8'></sub></form><legend id='1zch8'></legend><bdo id='1zch8'><pre id='1zch8'><center id='1zch8'></center></pre></bdo></b><th id='1zch8'></th></span></q></dt></tr></i><div id='1zch8'><tfoot id='1zch8'></tfoot><dl id='1zch8'><fieldset id='1zch8'></fieldset></dl></div>

        <small id='1zch8'></small><noframes id='1zch8'>

        <tfoot id='1zch8'></tfoot>
      1. <legend id='1zch8'><style id='1zch8'><dir id='1zch8'><q id='1zch8'></q></dir></style></legend>
          <bdo id='1zch8'></bdo><ul id='1zch8'></ul>

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

        Oracle pivot with subquery(带有子查询的 Oracle 数据透视表)
        • <small id='pvdQy'></small><noframes id='pvdQy'>

        • <tfoot id='pvdQy'></tfoot>

              <i id='pvdQy'><tr id='pvdQy'><dt id='pvdQy'><q id='pvdQy'><span id='pvdQy'><b id='pvdQy'><form id='pvdQy'><ins id='pvdQy'></ins><ul id='pvdQy'></ul><sub id='pvdQy'></sub></form><legend id='pvdQy'></legend><bdo id='pvdQy'><pre id='pvdQy'><center id='pvdQy'></center></pre></bdo></b><th id='pvdQy'></th></span></q></dt></tr></i><div id='pvdQy'><tfoot id='pvdQy'></tfoot><dl id='pvdQy'><fieldset id='pvdQy'></fieldset></dl></div>

              <legend id='pvdQy'><style id='pvdQy'><dir id='pvdQy'><q id='pvdQy'></q></dir></style></legend>
                <tbody id='pvdQy'></tbody>
                  <bdo id='pvdQy'></bdo><ul id='pvdQy'></ul>

                  本文介绍了带有子查询的 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 数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

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

                  相关文档推荐

                  How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?(如何将 DBMS_OUTPUT.PUT_LINE 的输出重定向到文件?)
                  How do I get column datatype in Oracle with PL-SQL with low privileges?(如何使用低权限的 PL-SQL 在 Oracle 中获取列数据类型?)
                  Get a list of all functions and procedures in an Oracle database(获取 Oracle 数据库中所有函数和过程的列表)
                  Why cannot I create triggers on objects owned by SYS?(为什么我不能在 SYS 拥有的对象上创建触发器?)
                  Returning result even for elements in IN list that don#39;t exist in table(即使对于表中不存在的 IN 列表中的元素也返回结果)
                  Reset Sequence in oracle 11g(oracle 11g 中的重置序列)
                    <bdo id='U800k'></bdo><ul id='U800k'></ul>

                      <tbody id='U800k'></tbody>
                    • <small id='U800k'></small><noframes id='U800k'>

                      <i id='U800k'><tr id='U800k'><dt id='U800k'><q id='U800k'><span id='U800k'><b id='U800k'><form id='U800k'><ins id='U800k'></ins><ul id='U800k'></ul><sub id='U800k'></sub></form><legend id='U800k'></legend><bdo id='U800k'><pre id='U800k'><center id='U800k'></center></pre></bdo></b><th id='U800k'></th></span></q></dt></tr></i><div id='U800k'><tfoot id='U800k'></tfoot><dl id='U800k'><fieldset id='U800k'></fieldset></dl></div>

                          <tfoot id='U800k'></tfoot><legend id='U800k'><style id='U800k'><dir id='U800k'><q id='U800k'></q></dir></style></legend>