Transforming row-oriented query results into a cross-tabular layout often makes data analysis more intuitive. Oracle’s PIVOT operator provides a concise way to acheive this, while ADD_MONTHS simplifies month‑based date calculations.

PIVOT: Row‑to‑Column Transformation

Consider a portfolio table that stores investment holdings:

SELECT account_id, product_type, SUM(quantity) AS total_qty
FROM portfolio
GROUP BY account_id, product_type;

The result lists one row per account and product. Too view each product type as a separate column for every account, a traditional approach uses conditional aggregation:

SELECT account_id,
       SUM(CASE WHEN product_type = 'BOND' THEN quantity ELSE 0 END)  AS bond_qty,
       SUM(CASE WHEN product_type = 'STOCK' THEN quantity ELSE 0 END) AS stock_qty,
       SUM(CASE WHEN product_type = 'ETF'   THEN quantity ELSE 0 END) AS etf_qty
FROM portfolio
GROUP BY account_id;

An equivalent DECODE version:

SELECT account_id,
       SUM(DECODE(product_type, 'BOND', quantity, 0)) AS bond_qty,
       SUM(DECODE(product_type, 'STOCK', quantity, 0)) AS stock_qty,
       SUM(DECODE(product_type, 'ETF', quantity, 0)) AS etf_qty
FROM portfolio
GROUP BY account_id;

The PIVOT operator delivers the same output with less repetition:

SELECT *
FROM (
  SELECT account_id, product_type, quantity
  FROM portfolio
)
PIVOT (
  SUM(quantity)
  FOR product_type IN ('BOND' AS bond_qty, 'STOCK' AS stock_qty, 'ETF' AS etf_qty)
);

Any aggregate function (SUM, AVG, MIN, MAX, etc.) can be used inside PIVOT. The inner query defines the source columns, the FOR clause specifies the column whose values become new column headers, and the IN list enumerates the desired values with aliases.

ADD_MONTHS: Shifting Dates by Months

ADD_MONTHS(date, integer) returns the date that is a specified number of months away from the given date. A positive integer moves forward, a negative one moves backward.

Retrieve the date two months from today:

SELECT ADD_MONTHS(SYSDATE, 2) FROM dual;

Get the date two months prior:

SELECT ADD_MONTHS(SYSDATE, -2) FROM dual;

When working with fixed date values, explicit conversion guaranteees correctness:

SELECT *
FROM orders
WHERE order_date >= TO_DATE('2024-03-01', 'YYYY-MM-DD')
  AND order_date <  ADD_MONTHS(TO_DATE('2024-03-01', 'YYYY-MM-DD'), 3);

This query selects orders placed from March 1, 2024 up to (but not including) June 1, 2024. The expression ADD_MONTHS(start, 3) defines the exclusive upper boundary for a three‑month window.