Pivot Table Development for Analytics
A pivot table is a UI for grouping, aggregating, and comparing data in real time. Users drag fields between axes, select metrics, and get needed slices without contacting developers. Excel and Google Sheets are popular precisely for this—embedding similar tools in web apps eliminates export needs.
Architecture
Two fundamentally different approaches:
Client aggregation—all data loaded in browser, pivot calculated in JavaScript. Works up to ~100–200k rows. Fast response on axis manipulation, no server round-trip.
Server aggregation—each config change sends a server request, database calculates aggregates. Mandatory for large volumes. ClickHouse or PostgreSQL with correct indexes return aggregates from millions of rows in hundreds of milliseconds.
Ready-Made Libraries
Before building from scratch, evaluate:
- react-pivottable—open source, drag-and-drop, multiple renderers (table, bar chart, heatmap). Limited to client processing
- AG Grid (with row grouping)—enterprise-grade, server mode, huge ecosystem. Paid for advanced features
- Flexmonster—specialized pivot, connects to OLAP cubes, commercial license
Client Implementation
Pivot core—aggregation function:
type AggregateFunction = 'sum' | 'count' | 'avg' | 'min' | 'max';
interface PivotConfig {
rows: string[]; // row fields
cols: string[]; // column fields
values: string[]; // numeric fields
aggFn: AggregateFunction;
filters: Record<string, string[]>; // field -> allowed values
}
interface PivotResult {
rowKeys: string[][];
colKeys: string[][];
data: Map<string, Map<string, number>>;
}
function computePivot(rawData: Record<string, any>[], config: PivotConfig): PivotResult {
const { rows, cols, values, aggFn, filters } = config;
// Apply filters
const filtered = rawData.filter(row =>
Object.entries(filters).every(([field, allowed]) =>
!allowed.length || allowed.includes(String(row[field]))
)
);
// Collect unique row and column keys
const rowKeySet = new Set<string>();
const colKeySet = new Set<string>();
const accumulator = new Map<string, Map<string, number[]>>();
filtered.forEach(row => {
const rowKey = rows.map(r => String(row[r] ?? '(empty)')).join('||');
const colKey = cols.map(c => String(row[c] ?? '(empty)')).join('||');
rowKeySet.add(rowKey);
colKeySet.add(colKey);
const numVal = values.reduce((sum, v) => sum + (Number(row[v]) || 0), 0);
if (!accumulator.has(rowKey)) accumulator.set(rowKey, new Map());
const colMap = accumulator.get(rowKey)!;
if (!colMap.has(colKey)) colMap.set(colKey, []);
colMap.get(colKey)!.push(numVal);
});
// Aggregate
const aggregated = new Map<string, Map<string, number>>();
accumulator.forEach((colMap, rowKey) => {
const row = new Map<string, number>();
colMap.forEach((vals, colKey) => {
let result: number;
switch (aggFn) {
case 'sum': result = vals.reduce((a, b) => a + b, 0); break;
case 'count': result = vals.length; break;
case 'avg': result = vals.reduce((a, b) => a + b, 0) / vals.length; break;
case 'min': result = Math.min(...vals); break;
case 'max': result = Math.max(...vals); break;
}
row.set(colKey, result);
});
aggregated.set(rowKey, row);
});
return {
rowKeys: Array.from(rowKeySet).sort().map(k => k.split('||')),
colKeys: Array.from(colKeySet).sort().map(k => k.split('||')),
data: aggregated,
};
}
Table Component
Custom pivot UI renders efficiently with virtualized rows:
Key features:
- Drag-and-drop axis configuration
- Real-time aggregation
- Subtotals and grand total
- Export to CSV/Excel
- Conditional formatting for values
Server Implementation
For millions of rows, use window functions:
SELECT
date_trunc('month', created_at)::date AS month,
category,
SUM(amount) AS total,
COUNT(*) AS cnt,
AVG(amount) AS avg_amount
FROM orders
WHERE created_at > NOW() - INTERVAL '12 months'
GROUP BY 1, 2
ORDER BY 1, 2;
Indexing on (category, created_at) or composite indexes dramatically speeds aggregation.
Timeline
Basic client-side pivot with drag-drop UI—3–5 days. Server implementation with filtering, drill-down, and export—7–10 days.