Reshaping Data with Window Logic

The combination that does it is a window function to compute the value, then conditional aggregation to spread it across columns. The window function gives every row its group's value; the CASE expression inside an aggregate decides which column each row's value belongs in.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (id, customer_id, amount, placed_on) VALUES
  (1, 1, 120.00, '2026-01-05'),
  (2, 1, 340.50, '2026-01-20'),
  (3, 2,  75.25, '2026-02-03'),
  (4, 1,  90.00, '2026-02-14');

SELECT id,
       customer_id,
       amount,
       MAX(amount) OVER (PARTITION BY customer_id) AS largest_order,
       MIN(amount) OVER (PARTITION BY customer_id) AS smallest_order,
       FIRST_VALUE(amount) OVER (
         PARTITION BY customer_id
         ORDER BY amount DESC
       ) AS top_order
FROM orders
ORDER BY customer_id, id;

Four rows come back. Ada's three orders each carry her largest order of 340.50 and her smallest of 90.00, and every one of them carries 340.50 as the top order. Grace's single order carries 75.25 for all three.

Each of the three window functions does the same job by a different route. MAX and MIN over the partition compute the extremes directly. FIRST_VALUE with an ORDER BY inside the window takes the value from the first row of the ordered partition, which is the largest order because the ordering is by amount descending. That one is the most useful of the three, because it can pull any field from the leading row of a group — the customer's name, the first order date, the latest status.

Two things to keep in mind. The partition determines the row's group, and it is entirely independent of the ORDER BY on the final output — so you can sort the report however you like without disturbing the numbers. And the window functions do not reduce the row count, so this is an annotation, not a reshape. To actually reshape into fewer, wider rows, you need the conditional aggregation in the next answer.

SQL pivots with conditional aggregation: one aggregate expression per output column, each one a CASE that selects the rows belonging to that column. Application-code pivots by pulling the rows out and rearranging them in memory.

The difference is where the work happens and how much data crosses the boundary. A SQL pivot returns the wide result directly — four columns and a handful of rows. An application-code pivot has to pull every source row across the network first, and if there are a million of them, that is a million rows moved to produce four columns and ten rows.

That difference matters most for large data. A report that shows revenue per month per region has twelve rows in the source and one row per region in the output. Doing that pivot in SQL transfers twelve rows; doing it in the application transfers twelve rows too, but the application now has to understand the shape of the data and hold the logic. At a million source rows, the SQL version transfers twelve rows and the application version transfers a million, and the query time becomes network time.

There are arguments for pivoting in the application. It keeps the SQL simple and portable — the same long-format query works for every report, and the reshaping lives with the report. It is easier to change when the report changes, because you do not have to rewrite the SQL and redeploy. And it works with any data store, including a REST API that returns JSON.

The best answer in most systems is to keep the data long in the database and pivot in the presentation layer — a BI tool, a reporting library, or the front end. The database should store facts in a shape that is easy to query and easy to aggregate; the reshaping into columns is a presentation concern. Reserve the SQL pivot for the cases where the wide shape is genuinely the output — a CSV export, a fixed-format report, or a table that is itself the pivot.

Compute the running total with a window function, then pivot it with conditional aggregation. The inner query produces one row per order; the outer query collapses to one row per date with a column per customer.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (id, customer_id, amount, placed_on) VALUES
  (1, 1, 120.00, '2026-01-05'),
  (2, 1, 340.50, '2026-01-20'),
  (3, 2,  75.25, '2026-02-03'),
  (4, 1,  90.00, '2026-02-14');

SELECT placed_on,
       ROUND(MAX(CASE WHEN customer_id = 1 THEN running_total END), 2) AS customer_1_running,
       ROUND(MAX(CASE WHEN customer_id = 2 THEN running_total END), 2) AS customer_2_running
FROM (
  SELECT id, customer_id, amount, placed_on,
         SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY placed_on
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS running_total
  FROM orders
)
GROUP BY placed_on
ORDER BY placed_on;

Four rows come back. On 2026-01-05 customer 1's running total is 120.00 and customer 2's is NULL, since she has not ordered yet. On 2026-01-20 customer 1 is at 460.50, on 2026-02-03 customer 2 appears at 75.25, and on 2026-02-14 customer 1 is at 550.50.

Two stages doing two different jobs. The window function produces the running total per customer, one value per order. The outer query then aggregates to one row per date, and the CASE inside MAX decides which column each row's value belongs in.

The MAX is doing something specific, and it is worth understanding rather than copying. On a given date, customer 1 may have several orders, each with a different running total, and the running total is cumulative, so the largest one is the final value for that date. MAX picks it. Using SUM would add the running totals together, which is nonsense; using MIN would take the earliest. If a customer can have several orders on the same date, this pattern still gives the right answer, because the running total only ever increases.

The NULLs in the empty cells are the honest representation — that customer had no order on that date. If the report needs zeros instead, wrap the CASE in COALESCE with a zero, and decide deliberately whether a zero means "no order" or "an order worth nothing".