How can window functions be used to reshape rows into columns?
You use the conditional aggregation inside a window function, and the window makes the calculation apply to each group separately.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
So what is the shape of it? You take the value that you want in each column, and you make the calculation pick out only the rows that belong in that column. Then the window says which rows it applies to.
-- A pivot that uses the window function.
SELECT
id,
MAX(CASE WHEN customer_id = 1 THEN amount ELSE 0 END)
OVER (PARTITION BY customer_id) AS customer_one_amount,
MAX(CASE WHEN customer_id = 2 THEN amount ELSE 0 END)
OVER (PARTITION BY customer_id) AS customer_two_amount
FROM orders;
So what does the partition do here? It makes each calculation run inside one customer's orders, so the value for customer one appears beside customer one's rows and the value for customer two appears beside customer two's rows.
There is one thing to be honest about here, and it is that the ordinary conditional aggregation does this job better. The pivot that people actually write is the one with the group by, and the window version is worth knowing for the cases where you need the detail rows as well as the pivoted columns.
The next question in this topic compares pivoting in the database with pivoting in your program.