Pivoting tables

T

This article is dedicated to my colleague and keen learner strugling today with pivot query.

This is modern version of pivot query introduced in Oracle 11g. Bad part is you have to list columns.

WITH d AS (
    SELECT 'A' AS group_name, 'A' AS col_name, 10 AS col_value FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 'B' AS col_name, 20 AS col_value FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 'C' AS col_name, 30 AS col_value FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 'D' AS col_name, 60 AS col_value FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 'A' AS col_name, 10 AS col_value FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 'B' AS col_name, 20 AS col_value FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 'C' AS col_name, 30 AS col_value FROM DUAL
)
SELECT d.*
FROM d
PIVOT (
    SUM(d.col_value)    AS sum,
    COUNT(d.col_value)  AS count
    FOR col_name
    IN (
        'A' AS a,
        'B' AS b,
        'C' AS c,
        'D' AS d
    )
) d
ORDER BY 1;

You can do dynamic columns only when output is XML.

WITH d AS (
    SELECT 'A' AS group_name, 'A' AS col_name, 10 AS col_value FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 'B' AS col_name, 20 AS col_value FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 'C' AS col_name, 30 AS col_value FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 'D' AS col_name, 60 AS col_value FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 'A' AS col_name, 10 AS col_value FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 'B' AS col_name, 20 AS col_value FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 'C' AS col_name, 30 AS col_value FROM DUAL
)
SELECT d.group_name, TRIM(d.col_name_xml) AS xml
FROM d
PIVOT XML (                         -- XML
    SUM(col_value)    AS sum,
    COUNT(col_value)  AS count
    FOR col_name
    IN (
        SELECT DISTINCT d.col_name  -- dynamic columns possible in XML only
        FROM d
    )
) d
ORDER BY 1;

You can convert XML back to table.

SELECT *
FROM XMLTABLE (
    '/PivotSet/item'
    PASSING XMLTYPE('A101B201C301D601')
    COLUMNS
        col_name    VARCHAR2(30)    PATH 'column[@name="COL_NAME"]',
        sum_        NUMBER          PATH 'column[@name="SUM"]',
        count_      NUMBER          PATH 'column[@name="COUNT"]'
);

You can also convert it to JSON.

SELECT JSON_OBJECTAGG(col_name VALUE JSON_OBJECT('SUM' VALUE sum_, 'COUNT' VALUE count_)) AS json
FROM XMLTABLE (
    '/PivotSet/item'
    PASSING XMLTYPE('A101B201C301D601')
    COLUMNS
        col_name    VARCHAR2(30)    PATH 'column[@name="COL_NAME"]',
        sum_        NUMBER          PATH 'column[@name="SUM"]',
        count_      NUMBER          PATH 'column[@name="COUNT"]'
);

And of course you can reverse pivoted table.

WITH d AS (
    SELECT 'A' AS group_name, 11 AS col_a, 12 AS col_b, 13 AS col_c, 14 AS col_d FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 21 AS col_a, 22 AS col_b, 23 AS col_c, 24 AS col_d FROM DUAL
)
SELECT *
FROM d
UNPIVOT (
    col_value FOR col_name IN (
        col_a AS 'A',
        col_b AS 'B',
        col_c AS 'C',
        col_d AS 'D'
    )
);