Oracle pivot with subquery

Question

I am 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 edit every time a new column is added or one changes (for example, Group4, 5, 6, etc.), so I tried the subquery 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: expression missing.

After some research, it turned out that I can generate the results using XML, so I tried the following:

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

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

Answer 1

Could you use the PIPELINED function to achieve your goal?

I wrote an example of such a function. The example is based on a table, sample data, and a PIVOT query from Tom Kyte articles that you can find on his website:

Tom Keith article on PIVOT / UNPIVOT

Tom Keith article on PIPELINED features

An example works as follows.

We create two types:

• t_pivot_test_obj - the type that contains the columns that we want to extract from XML
• t_pivot_test_obj_tab - type of nested table of specified objects.

Then we create a PIPELINED function that contains a query with PIVOT that generates XML (so you don’t need to hardcode the values ​​you want to flip). This function extracts data from the generated XML and passes the string (PIPE) to the calling request as it is created (on the fly - it is not generated immediately, which is important for performance).

Finally, you write a query that selects records from this function (at the end this 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 

Answer 2

You can generate the text of your first SQL statement by iterating and then executing this statement separately.

If you are not opposed to a quasi-dynamic solution, you can plan to create a VIEW this way using dynamic SQL (i.e. EXECUTE IMMEDIATE).

(The Crystal report, as far as I know, should know the column names in advance.)

Edited to add code. I have not tested this. Also note that this will be interrupted if the SQL statement exceeds 32 Kbytes, regardless of the actual number of multibyte characters.

 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 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; /