带有子查询的Oracle Pivot [英] Oracle pivot with subquery
问题描述
我在Oracle PL SQL Developer中使用枢轴，如下所示:
SELECT *
FROM population
PIVOT (AVG(Total) for Data_Type IN ('Group1','Group2','Group3'))
这很好用，但是我不想每次添加新列或更改一个新列(即Group4、5、6等)时都不必进行编辑，所以我尝试了如下子查询:
SELECT *
FROM population
PIVOT (AVG(Total) for Data_Type IN (SELECT Data_Type FROM population))
这将导致以下错误:ORA-00936:缺少表达式.
经过研究，看来我可以使用XML生成结果，所以我尝试了以下操作:
SELECT *
FROM population
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 (
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())
;
输出:
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
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 Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
