PIVOT, APPLY and windows in SQL Server and Oracle

Three parts of a report query differ most between SQL Server and Oracle. PIVOT turns the values of one column into columns: both engines have the operator, but SQL Server’s takes one aggregate with the values in brackets, while Oracle’s takes several aggregates with quoted values and builds the column names from aliases. APPLY runs a subquery once per row of the table before it, which is how you fetch “the largest order of each customer”; SQL Server has CROSS APPLY and OUTER APPLY, and Oracle accepts both, as well as LATERAL. Window functions are where the two differ in the details: Oracle runs RANGE frames with offsets, GROUPS frames and QUALIFY, while the windows in SQL Server allow RANGE only up to UNBOUNDED and CURRENT ROW. Conditional aggregation, ROW_NUMBER in a CTE and a calendar table are the forms that run unchanged on both.

What a PIVOT does

A PIVOT groups the rows by every column it does not name, then makes one output column for each value in its IN list. The list is fixed when the query is written, so a new value in the data appears in no column: a report silently loses it.

One matrix, three ways of writing it

-- A city by payment-mode matrix, built with conditional aggregation: the portable form of what both engines' PIVOT
-- operators compute. For comparison, the operators would read:
--   SQL Server:  SELECT city, [UPI], [Card], [Cash]
--                FROM (SELECT city, mode, amount FROM payments) AS src
--                PIVOT (SUM(amount) FOR mode IN ([UPI], [Card], [Cash])) AS p;
--   Oracle:      SELECT * FROM (SELECT city, mode, amount FROM payments)
--                PIVOT (SUM(amount) AS total, COUNT(*) AS cnt FOR mode IN ('UPI' AS upi, 'Card' AS card, 'Cash' AS cash));
-- SQL Server takes one aggregate; Oracle takes several and names the columns UPI_TOTAL, UPI_CNT and so on.
CREATE TABLE payments (id INTEGER PRIMARY KEY, city TEXT NOT NULL, mode TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO payments (city, mode, amount) VALUES
  ('Pune', 'UPI', 450), ('Pune', 'UPI', 1200), ('Pune', 'Card', 3000), ('Pune', 'Cash', 200),
  ('Kochi', 'UPI', 700), ('Kochi', 'Cash', 150), ('Kochi', 'Cash', 90),
  ('Indore', 'Card', 2500), ('Indore', 'UPI', 380), ('Indore', 'Wallet', 120);

-- 1. One aggregate per mode, as SQL Server's PIVOT gives it: a mode a city never used is NULL.
SELECT city,
       sum(CASE WHEN mode = 'UPI'  THEN amount END) AS upi,
       sum(CASE WHEN mode = 'Card' THEN amount END) AS card,
       sum(CASE WHEN mode = 'Cash' THEN amount END) AS cash
FROM payments
GROUP BY city
ORDER BY city;

-- 2. Two aggregates per mode, as Oracle's PIVOT can give them, with zeros instead of NULLs and a total that also
--    counts the 'Wallet' payment the fixed list of columns leaves out.
SELECT city,
       coalesce(sum(CASE WHEN mode = 'UPI' THEN amount END), 0) AS upi_total,
       count(CASE WHEN mode = 'UPI' THEN 1 END)                   AS upi_cnt,
       coalesce(sum(CASE WHEN mode = 'Card' THEN amount END), 0) AS card_total,
       count(CASE WHEN mode = 'Card' THEN 1 END)                  AS card_cnt,
       sum(amount)                                                AS all_modes,
       sum(CASE WHEN mode NOT IN ('UPI', 'Card', 'Cash') THEN amount ELSE 0 END) AS not_in_list
FROM payments
GROUP BY city
ORDER BY city;

┌────────┬──────┬──────┬──────┐
│  city  │ upi  │ card │ cash │
├────────┼──────┼──────┼──────┤
│ Indore │ 380  │ 2500 │      │
│ Kochi  │ 700  │      │ 240  │
│ Pune   │ 1650 │ 3000 │ 200  │
└────────┴──────┴──────┴──────┘
┌────────┬───────────┬─────────┬────────────┬──────────┬───────────┬─────────────┐
│  city  │ upi_total │ upi_cnt │ card_total │ card_cnt │ all_modes │ not_in_list │
├────────┼───────────┼─────────┼────────────┼──────────┼───────────┼─────────────┤
│ Indore │ 380       │ 1       │ 2500       │ 1        │ 3000      │ 120         │
│ Kochi  │ 700       │ 1       │ 0          │ 0        │ 940       │ 0           │
│ Pune   │ 1650      │ 2       │ 3000       │ 1        │ 4850      │ 0           │
└────────┴───────────┴─────────┴────────────┴──────────┴───────────┴─────────────┘

The first result is what SQL Server’s PIVOT (SUM(amount) FOR mode IN (...)) returns: one aggregate, and NULL in the cells where a city never used a mode. The second is what Oracle’s PIVOT with two aliased aggregates can return, a total and a count per mode, with zeros where a mode was not used. Each CASE returns the amount for one mode and NULL for the rest, so sum adds only that mode. The last column counts what any fixed list misses: Indore’s wallet payment of 120 is in no mode column.

Conditional aggregation is the form to prefer when a query must run on both engines. It takes any number of aggregates, needs no brackets or quoted names, and turns NULLs into zeros where you ask. The operators also have rules of their own: SQL Server’s PIVOT refuses COUNT(*) and returns 0 where a COUNT finds no rows, and Oracle’s names a column after the value alone when the aggregate has no alias, so the same result set can carry different column names in the two engines.

UNPIVOT and the NULL cells

UNPIVOT turns columns back into rows, and both engines leave out NULL cells by default. Oracle’s UNPIVOT INCLUDE NULLS keeps them; SQL Server has no such option:

-- Quarterly targets kept one column per quarter, turned back into rows. Both engines' UNPIVOT leave out NULL cells by
-- default; Oracle's UNPIVOT INCLUDE NULLS keeps them. UNION ALL is the portable form, and its WHERE decides the same.
CREATE TABLE targets (city TEXT PRIMARY KEY, q1 INTEGER, q2 INTEGER, q3 INTEGER, q4 INTEGER);
INSERT INTO targets VALUES ('Pune', 900, 950, 1000, NULL), ('Kochi', 400, NULL, 450, 500);

WITH unpivoted (city, quarter, target) AS (
  SELECT city, 'Q1', q1 FROM targets UNION ALL
  SELECT city, 'Q2', q2 FROM targets UNION ALL
  SELECT city, 'Q3', q3 FROM targets UNION ALL
  SELECT city, 'Q4', q4 FROM targets
)
SELECT 'like UNPIVOT (the default)' AS form, count(*) AS rows_out FROM unpivoted WHERE target IS NOT NULL
UNION ALL
SELECT 'like UNPIVOT INCLUDE NULLS', count(*) FROM unpivoted;

-- The cells that the default leaves out: a quarter without a target simply disappears from the result.
WITH unpivoted (city, quarter, target) AS (
  SELECT city, 'Q1', q1 FROM targets UNION ALL
  SELECT city, 'Q2', q2 FROM targets UNION ALL
  SELECT city, 'Q3', q3 FROM targets UNION ALL
  SELECT city, 'Q4', q4 FROM targets
)
SELECT city, quarter FROM unpivoted WHERE target IS NULL ORDER BY city, quarter;

┌────────────────────────────┬──────────┐
│            form            │ rows_out │
├────────────────────────────┼──────────┤
│ like UNPIVOT (the default) │ 6        │
│ like UNPIVOT INCLUDE NULLS │ 8        │
└────────────────────────────┴──────────┘
┌───────┬─────────┐
│ city  │ quarter │
├───────┼─────────┤
│ Kochi │ Q2      │
│ Pune  │ Q4      │
└───────┴─────────┘

Eight cells become six rows with the default rule, and two quarters without a target vanish. A report built on those rows cannot tell “no target” from “no row”, so decide which one the data means before unpivoting. The columns being turned into rows must also share a type: Oracle’s reference requires one data type group, numeric or character, so a mix of number and text columns has to be converted first.

APPLY and the window differences, in brief

APPLY is the natural form when the subquery needs its own ORDER BY and row limit for each outer row, which a plain join cannot express. The CROSS APPLY form returns the rows of the subquery for each outer row, and drops outer rows without any. Its partner, OUTER APPLY, keeps those outer rows, with NULLs. PostgreSQL writes the same as LEFT JOIN LATERAL (...) ON true. QUALIFY filters on a window function’s result in Oracle. Named windows in SQL Server need version 2022 and compatibility level 160, and QUALIFY does not exist there, so a query that filters on a window function needs a CTE around it. GROUPS frames count groups of tied rows. KEEP (DENSE_RANK FIRST) returns a value from the top-ranked rows inside a GROUP BY, in Oracle only.

Your turn

-- One row per city: the amounts paid by upi, card and cash, with 0 where a city has none.
-- This starter adds up every payment of the city in each column.
SELECT city,
       sum(amount) AS upi,
       sum(amount) AS card,
       sum(amount) AS cash
FROM payments
GROUP BY city;

# Sample tests: each one builds a small payments table, runs your query on it and compares the rows it returns (in any
# order).
tests:
  - name: two cities, every mode used somewhere
    setup: |
      CREATE TABLE payments (id INTEGER PRIMARY KEY, city TEXT NOT NULL, mode TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO payments VALUES
        (1, 'Pune', 'upi', 450), (2, 'Pune', 'upi', 1200), (3, 'Pune', 'card', 3000), (4, 'Pune', 'cash', 200),
        (5, 'Kochi', 'upi', 700), (6, 'Kochi', 'cash', 150), (7, 'Kochi', 'cash', 90);
    expected:
      columns: [city, upi, card, cash]
      rows:
        - [Pune, 1650, 3000, 200]
        - [Kochi, 700, 0, 240]
  - name: a city that paid only by card
    setup: |
      CREATE TABLE payments (id INTEGER PRIMARY KEY, city TEXT NOT NULL, mode TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO payments VALUES
        (1, 'Indore', 'card', 2500), (2, 'Indore', 'card', 500), (3, 'Surat', 'upi', 80);
    expected:
      columns: [city, upi, card, cash]
      rows:
        - [Indore, 0, 3000, 0]
        - [Surat, 80, 0, 0]
  - name: modes outside the report are not counted in its columns
    setup: |
      CREATE TABLE payments (id INTEGER PRIMARY KEY, city TEXT NOT NULL, mode TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO payments VALUES
        (1, 'Nagpur', 'wallet', 120), (2, 'Nagpur', 'cash', 60), (3, 'Nagpur', 'upi', 300);
    expected:
      columns: [city, upi, card, cash]
      rows:
        - [Nagpur, 300, 0, 60]

The top row per group, five ways compared, with ties and missing rows Pro: opens with a Pro, Premium or Ultimate pass

Window frames, rolling sums and carried-forward values, ported between the engines Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• SQL Server’s PIVOT takes one aggregate; Oracle’s takes several. Conditional aggregation runs on both.
• UNPIVOT drops NULL cells by default in both engines; Oracle can keep them with INCLUDE NULLS.
• CROSS APPLY and OUTER APPLY work in both engines, and PostgreSQL writes them with LATERAL.
• SQL Server allows RANGE frames only up to UNBOUNDED and CURRENT ROW, and has no GROUPS or QUALIFY.