Definition
- PIVOT operation – rotates row values into columns, turning distinct values of one column into separate output columns and aggregating a measure within each.
- Row dimension – the column that stays as rows in the output (for example,
customer_idordepartment). - Pivot column – the column whose distinct values become new output columns (for example,
channelproducing ATM, UPI, NEFT columns). - Measure – the value that is aggregated inside each cell (for example,
SUM(amount)orAVG(salary)). - Portable form –
SUM(CASE WHEN channel = 'ATM' THEN amount ELSE 0 END)reproduces the PIVOT on any database without a vendor keyword.
Imagine the finance team at the National Bank of India has a long list of transactions, one row per transaction, each tagged with a channel like ATM, UPI, or NetBanking. A PIVOT turns that tall list into a wide table where ATM, UPI, and NetBanking each become their own column, and each cell holds the total for that channel per customer. It is like taking a vertical ledger and laying it out as a cross-tab spreadsheet.
Understanding the Concepts
What is a PIVOT operation?
A PIVOT rotates row values into columns, producing a crosstab (cross-tabulation) report. It has three ingredients: the row dimension that stays as rows, the pivot column whose distinct values each become a new output column, and the measure that is aggregated inside each cell.
-- Sample data at National Bank of India
CREATE TABLE transactions (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
channel VARCHAR(20) NOT NULL,
amount DECIMAL(15,2) NOT NULL
);
INSERT INTO transactions (customer_id, channel, amount) VALUES
(1001, 'ATM', 25000.00),
(1001, 'UPI', 45000.00),
(1001, 'NEFT', 80000.00),
(1002, 'ATM', 15000.00),
(1002, 'UPI', 32000.00),
(1003, 'NEFT', 55000.00);
The raw data is tall (one row per transaction). The pivoted report is wide (one row per customer, one column per channel).
How do you PIVOT with the SQL Server keyword?
SQL Server and Oracle provide a dedicated PIVOT keyword. You specify the aggregate, the pivot column, and the values to pivot on.
-- SQL Server PIVOT syntax
SELECT customer_id, [ATM], [UPI], [NEFT]
FROM (
SELECT customer_id, channel, amount
FROM transactions
) src
PIVOT (
SUM(amount)
FOR channel IN ([ATM], [UPI], [NEFT])
) pvt;
customer_id | ATM | UPI | NEFT
-------------+----------+----------+----------
1001 | 25000.00 | 45000.00 | 80000.00
1002 | 15000.00 | 32000.00 | NULL
1003 | NULL | NULL | 55000.00
How do you achieve the same with portable conditional aggregation?
When the engine has no PIVOT keyword (PostgreSQL, MySQL), you use GROUP BY combined with one SUM(CASE WHEN ...) expression per target column. The CASE returns the amount only for matching rows, so summing gives the per-channel total.
-- Portable conditional aggregation (works on ALL databases)
SELECT customer_id,
SUM(CASE WHEN channel = 'ATM' THEN amount ELSE 0 END) AS atm_total,
SUM(CASE WHEN channel = 'UPI' THEN amount ELSE 0 END) AS upi_total,
SUM(CASE WHEN channel = 'NEFT' THEN amount ELSE 0 END) AS neft_total
FROM transactions
GROUP BY customer_id
ORDER BY customer_id;
customer_id | atm_total | upi_total | neft_total
-------------+-----------+-----------+-----------
1001 | 25000.00 | 45000.00 | 80000.00
1002 | 15000.00 | 32000.00 | 0.00
1003 | 0.00 | 0.00 | 55000.00
How do you pivot with AVG or COUNT instead of SUM?
Switch the aggregate function and adjust the ELSE clause. For AVG, omit the ELSE so non-matching rows become NULL (which AVG ignores). For COUNT, use COUNT(CASE WHEN ... THEN 1 END) since NULL is not counted.
-- AVG pivot: average transaction amount per channel per customer
SELECT customer_id,
AVG(CASE WHEN channel = 'ATM' THEN amount END) AS atm_avg,
AVG(CASE WHEN channel = 'UPI' THEN amount END) AS upi_avg,
AVG(CASE WHEN channel = 'NEFT' THEN amount END) AS neft_avg
FROM transactions
GROUP BY customer_id;
-- COUNT pivot: number of transactions per channel per customer
SELECT customer_id,
COUNT(CASE WHEN channel = 'ATM' THEN 1 END) AS atm_count,
COUNT(CASE WHEN channel = 'UPI' THEN 1 END) AS upi_count,
COUNT(CASE WHEN channel = 'NEFT' THEN 1 END) AS neft_count
FROM transactions
GROUP BY customer_id;
How is the portable form different from the PIVOT keyword?
| Aspect | PIVOT Keyword | Conditional Aggregation |
|---|---|---|
| Databases | SQL Server, Oracle | All databases |
| Readability | Declarative | Slightly verbose |
| Multiple measures | One per PIVOT | Multiple in same query |
| Dynamic columns | Needs dynamic SQL | Needs dynamic SQL |
| Column set | Fixed at write time | Fixed at write time |
Both approaches require you to know the pivot values at query-write time. If a new channel appears, you must add a new column in both cases.
What is the limitation of fixed column sets?
The column set is determined when you write the query. If a new channel like "IMPS" is added, you must update the query. For truly dynamic pivots, you need to generate the SQL programmatically.
package com.bank.service;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class DynamicPivotService {
public String buildPivotQuery(Connection conn) throws Exception {
// Step 1: discover distinct channel values
List<String> channels = new ArrayList<>();
try (Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(
"SELECT DISTINCT channel FROM transactions ORDER BY channel")) {
while (rs.next()) {
channels.add(rs.getString(1));
}
}
// Step 2: build a CASE expression for each channel
StringBuilder sb = new StringBuilder("SELECT customer_id");
for (String ch : channels) {
sb.append(", SUM(CASE WHEN channel = '")
.append(ch)
.append("' THEN amount ELSE 0 END) AS ")
.append(ch.toLowerCase()).append("_total");
}
sb.append(" FROM transactions GROUP BY customer_id ORDER BY customer_id");
return sb.toString();
}
}
What about emitting multiple measures per pivot column?
Conditional aggregation lets you emit both a sum and a count per channel in the same query, which the PIVOT keyword cannot do in a single operation.
SELECT customer_id,
SUM(CASE WHEN channel = 'ATM' THEN amount ELSE 0 END) AS atm_total,
COUNT(CASE WHEN channel = 'ATM' THEN 1 END) AS atm_count,
SUM(CASE WHEN channel = 'UPI' THEN amount ELSE 0 END) AS upi_total,
COUNT(CASE WHEN channel = 'UPI' THEN 1 END) AS upi_count
FROM transactions
GROUP BY customer_id;
customer_id | atm_total | atm_count | upi_total | upi_count
-------------+-----------+-----------+-----------+----------
1001 | 25000.00 | 1 | 45000.00 | 1
1002 | 15000.00 | 1 | 32000.00 | 1
1003 | 0.00 | 0 | 0.00 | 0
What about PostgreSQL's crosstab function?
PostgreSQL provides tablefunc extension with a crosstab function that can generate pivot tables. However, the portable conditional aggregation is preferred in most banking environments because it does not require an extension and is understood by every developer.
-- Enable the extension (once per database)
CREATE EXTENSION IF NOT EXISTS tablefunc;
-- crosstab approach
SELECT * FROM crosstab(
'SELECT customer_id, channel, SUM(amount)
FROM transactions
GROUP BY customer_id, channel
ORDER BY 1, 2',
'SELECT DISTINCT channel FROM transactions ORDER BY 1'
) AS ct(customer_id INT, atm DECIMAL, neft DECIMAL, upi DECIMAL);
What should a senior engineer know?
- The portable conditional aggregation form is preferred at the National Bank of India because dashboards run against different database engines and the column set is known and stable.
- For UNPIVOT (the reverse: turning columns back into rows), use
LATERALwithVALUESin PostgreSQL orUNPIVOTin SQL Server. - When the pivot produces sparse data (many NULLs or zeros), consider whether the tall format is actually better for downstream processing.
- In reporting tools like Tableau or Metabase, the pivot is done at the visualization layer, not in SQL.
What are common interview follow-up questions?
- What is UNPIVOT? (Turns columns back into rows – the reverse of PIVOT.)
- Can you pivot with more than one aggregate? (Yes with conditional aggregation, no with a single PIVOT keyword.)
- How do you handle dynamic pivot values? (Build the SQL string programmatically with the distinct values.)
Visual Overview
Tall format (raw) Wide format (pivoted) ┌─────────────┬─────────┬────────┐ ┌─────────────┬───────┬───────┬────────┐ │ customer_id │ channel │ amount │ │ customer_id │ ATM │ UPI │ NEFT │ ├─────────────┼─────────┼────────┤ ├─────────────┼───────┼───────┼────────┤ │ 1001 │ ATM │ 25000 │ │ 1001 │ 25000 │ 45000 │ 80000 │ │ 1001 │ UPI │ 45000 │ → │ 1002 │ 15000 │ 32000 │ 0 │ │ 1001 │ NEFT │ 80000 │ │ 1003 │ 0 │ 0 │ 55000 │ │ 1002 │ ATM │ 15000 │ └─────────────┴───────┴───────┴────────┘ │ 1002 │ UPI │ 32000 │ │ 1003 │ NEFT │ 55000 │ SUM(CASE WHEN channel = 'ATM' └─────────────┴─────────┴────────┘ THEN amount ELSE 0 END)
Key Takeaway
A PIVOT operation rotates row values into columns. You pick a dimension that stays as rows, a column whose distinct values each become a new output column, and a measure that is aggregated inside every cell. At the National Bank of India, Pankaj turns a tall transactions list into a wide report where each payment channel – ATM, UPI, NEFT – becomes its own column with the total amount per customer.
When the engine has no PIVOT keyword (PostgreSQL, MySQL), you reproduce the identical result with conditional aggregation: SUM(CASE WHEN channel = 'ATM' THEN amount ELSE 0 END) as the ATM column. This works on every database, reads as plain standard SQL, and lets you emit several measures in one grouped pass – both a total and a count per channel side by side.
The one trade-off is that the column set is fixed when the query is written, so adding a new channel means adding a new CASE expression. At the National Bank of India, the channel list is stable, so this costs nothing in practice. For truly dynamic pivots, Rajesh builds the SQL string programmatically by first querying the distinct channel values.
Remember one sentence: PIVOT turns rows into columns, and SUM(CASE WHEN ... END) with GROUP BY does the same thing portably on any database.
---