Introduction
Turning rows into columns may sound like a simple rearrangement, but when dealing with large datasets and multiple grouping criteria, manual pivoting with CASE statements can be tedious and error-prone. Oracle’s PIVOT clause, available since Oracle Database 11g, streamlines this transformation. Combined with PL/SQL, you can automate transformations that adapt to changing data. In this tutorial, we’ll walk through each step to build, understand, and execute an Oracle PIVOT query in PL/SQL, ensuring you can apply these techniques immediately in your own projects.
Step 1: Prepare Your Sample Table
For demonstration, let’s create a sample table monthly_sales:
sql
CopyEdit
CREATE TABLE monthly_sales (
product VARCHAR2(50),
sales_month VARCHAR2(10),
amount NUMBER
);
INSERT INTO monthly_sales VALUES (‘Widget A’, ‘Jan’, 1000);
INSERT INTO monthly_sales VALUES (‘Widget A’, ‘Feb’, 1200);
INSERT INTO monthly_sales VALUES (‘Widget B’, ‘Jan’, 800);
INSERT INTO monthly_sales VALUES (‘Widget B’, ‘Mar’, 950);
— Add more rows as needed
COMMIT;
This table records monthly sales for different products.
Step 2: Write the Basic PIVOT Query
Begin by writing a PIVOT that converts months into columns:
sql
CopyEdit
SELECT *
FROM (
SELECT product, sales_month, amount
FROM monthly_sales
)
PIVOT (
SUM(amount) FOR sales_month IN (‘Jan’ AS Jan, ‘Feb’ AS Feb, ‘Mar’ AS Mar)
);
What happens here?
- Inner query selects the raw data.
- PIVOT clause aggregates amount by sales_month.
- The IN list defines the columns in the final output.
Step 3: Execute and Review the Result
Running the above query yields:
| PRODUCT | JAN | FEB | MAR |
| Widget A | 1000 | 1200 | NULL |
| Widget B | 800 | NULL | 950 |
Observe that missing combinations appear as NULL. Replace them if necessary:
sql
CopyEdit
SELECT product,
NVL(Jan, 0) AS Jan_Sales,
NVL(Feb, 0) AS Feb_Sales,
NVL(Mar, 0) AS Mar_Sales
FROM ( … pivot query … );
Step 4: Introduce PL/SQL for Parameterization
Hardcoding months isn’t flexible. Use PL/SQL parameters:
plsql
CopyEdit
DECLARE
v_year NUMBER := 2025;
v_sql VARCHAR2(2000);
BEGIN
v_sql := ‘SELECT * FROM (
SELECT product, sales_month, amount
FROM monthly_sales
WHERE EXTRACT(YEAR FROM SYSDATE) = ‘ || v_year || ‘
)
PIVOT (
SUM(amount) FOR sales_month IN (”Jan” AS Jan, ”Feb” AS Feb, ”Mar” AS Mar)
)’;
EXECUTE IMMEDIATE v_sql;
END;
Replace the static year with a bind variable or procedure parameter for more flexibility.
Step 5: Build a Stored Procedure
Encapsulate pivot logic in a stored procedure that accepts month list dynamically:
plsql
CopyEdit
CREATE OR REPLACE PROCEDURE pivot_sales(p_months IN VARCHAR2) AS
v_sql VARCHAR2(4000);
BEGIN
v_sql := ‘SELECT * FROM (
SELECT product, sales_month, amount
FROM monthly_sales
)
PIVOT (
SUM(amount) FOR sales_month IN (‘ || p_months || ‘)
)’;
OPEN :result_cursor FOR v_sql;
END;
Call it with:
plsql
CopyEdit
VARIABLE rc REFCURSOR;
EXEC pivot_sales(”’Jan” AS Jan, ”Feb” AS Feb, ”Mar” AS Mar’);
PRINT rc;
Step 6: Automate Dynamic Month List Generation
Generate the month list automatically using LISTAGG:
plsql
CopyEdit
DECLARE
v_month_list VARCHAR2(1000);
BEGIN
SELECT LISTAGG(””||sales_month||”’ AS ‘||sales_month, ‘, ‘)
INTO v_month_list
FROM (SELECT DISTINCT sales_month FROM monthly_sales);
DBMS_OUTPUT.PUT_LINE(‘Generated IN list: ‘ || v_month_list);
END;
Plug v_month_list into your procedure call to handle new months without code changes.
Step 7: Schedule the Report
Use DBMS_SCHEDULER to run your pivot procedure nightly:
plsql
CopyEdit
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘sales_pivot_job’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘pivot_sales’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=2; BYMINUTE=0’,
enabled => TRUE
);
END;
This job executes at 2:00 AM each day, refreshing your pivoted results.
Conclusion
Converting vertical data into horizontal, easily comparable formats no longer requires complex CASE expressions or front-end workarounds. With Oracle PIVOT and PL/SQL, you have a robust, performance-optimized means to build dynamic reports that adapt to changing data scenarios. By following this step-by-step tutorial, from table preparation to job scheduling, you’ll be able to transform raw datasets into clear, actionable insights with minimal manual intervention. Embrace Oracle PIVOT to streamline your reporting workflows and empower stakeholders with timely, accurate cross-tabular data.
