Oracle pivot with subquery

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 PIPELINEDAS  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

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

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

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

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.

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