Pivot Tables: Matrix Aggregation in Your Data Grid

1<template>
2  <div style="display: flex; flex-direction: column; gap: 12px; width: 100%">
3    <div style="display: flex; flex-wrap: wrap; gap: 8px">
4      <button
5        v-for="preset in pivotPresets"
6        :key="preset.id"
7        type="button"
8        @click="activeId = preset.id"
9        :style="{
10          padding: '6px 12px',
11          borderRadius: '6px',
12          border: 'none',
13          cursor: 'pointer',
14          fontSize: '13px',
15          fontWeight: 500,
16          background: preset.id === activeId ? '#2563eb' : '#e5e7eb',
17          color: preset.id === activeId ? '#fff' : '#374151',
18        }"
19      >
20        {{ preset.label }}
21      </button>
22    </div>
23    <SimpleTable
24      :default-headers="pivotDemoConfig.headers"
25      :rows="pivotDemoConfig.rows"
26      :pivot="active.pivot"
27      :column-resizing="true"
28      :expand-all="nestedRows"
29      :height="height"
30      :selectable-cells="true"
31      :theme="theme"
32    />
33  </div>
34</template>
35
36<script setup lang="ts">
37import { computed, ref } from "vue";
38import { SimpleTable } from "@simple-table/vue";
39import type { Theme } from "@simple-table/vue";
40import { pivotDemoConfig, pivotPresets } from "./pivot.demo-data";
41import "@simple-table/vue/styles.css";
42
43withDefaults(defineProps<{ height?: string | number; theme?: Theme }>(), {
44  height: "400px",
45});
46
47const activeId = ref(pivotPresets[0].id);
48const active = computed(
49  () => pivotPresets.find((p) => p.id === activeId.value) ?? pivotPresets[0]
50);
51const nestedRows = computed(() => active.value.pivot.rows.length > 1);
52</script>

1import { Component, Input } from "@angular/core";
2import { SimpleTableComponent } from "@simple-table/angular";
3import type { AngularHeaderObject, PivotConfig, Row, Theme } from "@simple-table/angular";
4import { pivotDemoConfig, pivotPresets, type PivotPreset } from "./pivot.demo-data";
5import "@simple-table/angular/styles.css";
6
7@Component({
8  selector: "pivot-demo",
9  standalone: true,
10  imports: [SimpleTableComponent],
11  template: `
12    <div style="display: flex; flex-direction: column; gap: 12px; width: 100%">
13      <div style="display: flex; flex-wrap: wrap; gap: 8px">
14        @for (preset of presets; track preset.id) {
15          <button
16            type="button"
17            (click)="selectPreset(preset)"
18            [style.padding]="'6px 12px'"
19            [style.borderRadius]="'6px'"
20            [style.border]="'none'"
21            [style.cursor]="'pointer'"
22            [style.fontSize]="'13px'"
23            [style.fontWeight]="500"
24            [style.background]="preset.id === activeId ? '#2563eb' : '#e5e7eb'"
25            [style.color]="preset.id === activeId ? '#fff' : '#374151'"
26          >
27            {{ preset.label }}
28          </button>
29        }
30      </div>
31      <simple-table
32        [rows]="rows"
33        [defaultHeaders]="headers"
34        [pivot]="pivot"
35        [columnResizing]="true"
36        [expandAll]="nestedRows"
37        [height]="height"
38        [selectableCells]="true"
39        [theme]="theme"
40      ></simple-table>
41    </div>
42  `,
43})
44export class PivotDemoComponent {
45  @Input() height: string | number = "400px";
46  @Input() theme?: Theme;
47
48  readonly rows: Row[] = pivotDemoConfig.rows;
49  readonly headers: AngularHeaderObject[] = pivotDemoConfig.headers;
50  readonly presets = pivotPresets;
51
52  activeId = pivotPresets[0].id;
53  pivot: PivotConfig = pivotPresets[0].pivot;
54  nestedRows = pivotPresets[0].pivot.rows.length > 1;
55
56  selectPreset(preset: PivotPreset): void {
57    this.activeId = preset.id;
58    this.pivot = preset.pivot;
59    this.nestedRows = preset.pivot.rows.length > 1;
60  }
61}
62
63
64// pivot.demo-data.ts
65import type { PivotConfig, AngularHeaderObject, Row } from "@simple-table/angular";
66
67export const pivotHeaders: AngularHeaderObject[] = [
68  { accessor: "region", label: "Region", width: 110, type: "string" },
69  { accessor: "country", label: "Country", width: 100, type: "string" },
70  { accessor: "category", label: "Category", width: 110, type: "string" },
71  { accessor: "product", label: "Product", width: 120, type: "string" },
72  { accessor: "channel", label: "Channel", width: 100, type: "string" },
73  { accessor: "year", label: "Year", width: 80, type: "number" },
74  { accessor: "quarter", label: "Quarter", width: 80, type: "string" },
75  {
76    accessor: "sales",
77    label: "Sales",
78    width: 100,
79    type: "number",
80    align: "right",
81    valueFormatter: ({ value }) =>
82      typeof value === "number" ? `$${value.toLocaleString()}` : "",
83  },
84  {
85    accessor: "units",
86    label: "Units",
87    width: 80,
88    type: "number",
89    align: "right",
90  },
91  {
92    accessor: "cost",
93    label: "Cost",
94    width: 100,
95    type: "number",
96    align: "right",
97    valueFormatter: ({ value }) =>
98      typeof value === "number" ? `$${value.toLocaleString()}` : "",
99  },
100];
101
102const REGIONS = ["West", "East", "North", "South"] as const;
103const COUNTRIES: Record<(typeof REGIONS)[number], string[]> = {
104  West: ["USA", "Canada"],
105  East: ["USA", "UK"],
106  North: ["Canada", "Sweden"],
107  South: ["Brazil", "Australia"],
108};
109const CATEGORIES = ["Hardware", "Software"] as const;
110const PRODUCTS: Record<(typeof CATEGORIES)[number], string[]> = {
111  Hardware: ["Widget", "Gadget", "Sensor"],
112  Software: ["License", "Subscription"],
113};
114const CHANNELS = ["Direct", "Partner", "Online"] as const;
115const YEARS = [2024, 2025] as const;
116const QUARTERS = ["Q1", "Q2", "Q3", "Q4"] as const;
117
118/** Sparse multi-dimension fact cube (~150–250 rows). */
119export function generatePivotRows(): Row[] {
120  const rows: Row[] = [];
121  let id = 1;
122  for (const region of REGIONS) {
123    for (const country of COUNTRIES[region]) {
124      for (const category of CATEGORIES) {
125        for (const product of PRODUCTS[category]) {
126          for (const channel of CHANNELS) {
127            for (const year of YEARS) {
128              for (const quarter of QUARTERS) {
129                if ((id + year + quarter.charCodeAt(1) + channel.length) % 5 !== 0) {
130                  id++;
131                  continue;
132                }
133                const base = 40 + ((id * 17) % 90);
134                rows.push({
135                  id: `r${id}`,
136                  region,
137                  country,
138                  category,
139                  product,
140                  channel,
141                  year,
142                  quarter,
143                  sales: base * 100,
144                  units: base,
145                  cost: Math.round(base * 55),
146                });
147                id++;
148              }
149            }
150          }
151        }
152      }
153    }
154  }
155  return rows;
156}
157
158export const pivotRows: Row[] = generatePivotRows();
159
160export type PivotPreset = {
161  id: string;
162  label: string;
163  pivot: PivotConfig;
164};
165
166export const pivotPresets: PivotPreset[] = [
167  {
168    id: "region-quarter",
169    label: "Region × Quarter",
170    pivot: {
171      rows: ["region"],
172      columns: ["quarter"],
173      values: [{ accessor: "sales", aggregation: { type: "sum" } }],
174    },
175  },
176  {
177    id: "nested-rows",
178    label: "Region → Product",
179    pivot: {
180      rows: ["region", "product"],
181      columns: ["quarter"],
182      values: [{ accessor: "sales", aggregation: { type: "sum" } }],
183    },
184  },
185  {
186    id: "category-year-quarter",
187    label: "Category × Year → Quarter",
188    pivot: {
189      rows: ["category"],
190      columns: ["year", "quarter"],
191      values: [{ accessor: "sales", aggregation: { type: "sum" } }],
192    },
193  },
194  {
195    id: "channel-quarter",
196    label: "Channel × Quarter",
197    pivot: {
198      rows: ["channel"],
199      columns: ["quarter"],
200      values: [
201        { accessor: "sales", aggregation: { type: "sum" }, label: "Sales" },
202        { accessor: "units", aggregation: { type: "sum" }, label: "Units" },
203      ],
204    },
205  },
206  {
207    id: "country-category",
208    label: "Country × Category",
209    pivot: {
210      rows: ["country"],
211      columns: ["category"],
212      values: [{ accessor: "sales", aggregation: { type: "average" } }],
213      showColumnTotals: false,
214    },
215  },
216  {
217    id: "values-only",
218    label: "Values only",
219    pivot: {
220      rows: ["region", "category"],
221      columns: [],
222      values: [
223        { accessor: "sales", aggregation: { type: "sum" } },
224        { accessor: "cost", aggregation: { type: "sum" } },
225      ],
226    },
227  },
228];
229
230export const pivotConfig: PivotConfig = pivotPresets[0].pivot;
231
232export const pivotDemoConfig = {
233  headers: pivotHeaders,
234  rows: pivotRows,
235  tableProps: { pivot: pivotConfig },
236  presets: pivotPresets,
237};
238

1<script lang="ts">
2  import { SimpleTable } from "@simple-table/svelte";
3  import type { Theme } from "@simple-table/svelte";
4  import { pivotDemoConfig, pivotPresets } from "./pivot.demo-data";
5  import "@simple-table/svelte/styles.css";
6
7  let { height = "400px", theme }: { height?: string | number; theme?: Theme } = $props();
8
9  let activeId = $state(pivotPresets[0].id);
10  const active = $derived(pivotPresets.find((p) => p.id === activeId) ?? pivotPresets[0]);
11  const nestedRows = $derived(active.pivot.rows.length > 1);
12</script>
13
14<div style="display: flex; flex-direction: column; gap: 12px; width: 100%">
15  <div style="display: flex; flex-wrap: wrap; gap: 8px">
16    {#each pivotPresets as preset}
17      <button
18        type="button"
19        onclick={() => (activeId = preset.id)}
20        style="padding: 6px 12px; border-radius: 6px; border: none; cursor: pointer; font-size: 13px; font-weight: 500; background: {preset.id ===
21        activeId
22          ? '#2563eb'
23          : '#e5e7eb'}; color: {preset.id === activeId ? '#fff' : '#374151'}"
24      >
25        {preset.label}
26      </button>
27    {/each}
28  </div>
29  <SimpleTable
30    defaultHeaders={pivotDemoConfig.headers}
31    rows={pivotDemoConfig.rows}
32    pivot={active.pivot}
33    columnResizing={true}
34    expandAll={nestedRows}
35    selectableCells={true}
36    {height}
37    {theme}
38  />
39</div>

1import { createMemo, createSignal, For } from "solid-js";
2import { SimpleTable } from "@simple-table/solid";
3import type { Theme } from "@simple-table/solid";
4import { pivotDemoConfig, pivotPresets } from "./pivot.demo-data";
5import "@simple-table/solid/styles.css";
6
7export default function PivotDemo(props: {
8  height?: string | number;
9  theme?: Theme;
10}) {
11  const [activeId, setActiveId] = createSignal(pivotPresets[0].id);
12  const active = createMemo(
13    () => pivotPresets.find((p) => p.id === activeId()) ?? pivotPresets[0]
14  );
15  const nestedRows = createMemo(() => active().pivot.rows.length > 1);
16
17  return (
18    <div style={{ display: "flex", "flex-direction": "column", gap: "12px", width: "100%" }}>
19      <div style={{ display: "flex", "flex-wrap": "wrap", gap: "8px" }}>
20        <For each={pivotPresets}>
21          {(preset) => {
22            const selected = () => preset.id === activeId();
23            return (
24              <button
25                type="button"
26                onClick={() => setActiveId(preset.id)}
27                style={{
28                  padding: "6px 12px",
29                  "border-radius": "6px",
30                  border: "none",
31                  cursor: "pointer",
32                  "font-size": "13px",
33                  "font-weight": 500,
34                  background: selected() ? "#2563eb" : "#e5e7eb",
35                  color: selected() ? "#fff" : "#374151",
36                }}
37              >
38                {preset.label}
39              </button>
40            );
41          }}
42        </For>
43      </div>
44      <SimpleTable
45        defaultHeaders={pivotDemoConfig.headers}
46        rows={pivotDemoConfig.rows}
47        pivot={active().pivot}
48        columnResizing
49        expandAll={nestedRows()}
50        height={props.height ?? "400px"}
51        selectableCells
52        theme={props.theme}
53      />
54    </div>
55  );
56}

1import { SimpleTableVanilla } from "simple-table-core";
2import type { Theme } from "simple-table-core";
3import { pivotDemoConfig, pivotPresets } from "./pivot.demo-data";
4import "simple-table-core/styles.css";
5
6export function renderPivotDemo(
7  container: HTMLElement,
8  options?: { height?: string | number; theme?: Theme }
9): SimpleTableVanilla {
10  let activeId = pivotPresets[0].id;
11  let table: SimpleTableVanilla | null = null;
12
13  const root = document.createElement("div");
14  root.style.cssText = "display:flex;flex-direction:column;gap:12px;width:100%";
15
16  const buttons = document.createElement("div");
17  buttons.style.cssText = "display:flex;flex-wrap:wrap;gap:8px";
18
19  const tableHost = document.createElement("div");
20  tableHost.style.cssText = "width:100%";
21
22  const paintButtons = () => {
23    buttons.replaceChildren();
24    for (const preset of pivotPresets) {
25      const btn = document.createElement("button");
26      btn.type = "button";
27      btn.textContent = preset.label;
28      const selected = preset.id === activeId;
29      btn.style.cssText = `padding:6px 12px;border-radius:6px;border:none;cursor:pointer;font-size:13px;font-weight:500;background:${
30        selected ? "#2563eb" : "#e5e7eb"
31      };color:${selected ? "#fff" : "#374151"}`;
32      btn.addEventListener("click", () => {
33        activeId = preset.id;
34        paintButtons();
35        const active = pivotPresets.find((p) => p.id === activeId) ?? pivotPresets[0];
36        table?.updateConfig({
37          pivot: active.pivot,
38          expandAll: active.pivot.rows.length > 1,
39        });
40      });
41      buttons.appendChild(btn);
42    }
43  };
44
45  paintButtons();
46  root.append(buttons, tableHost);
47  container.replaceChildren(root);
48
49  const active = pivotPresets.find((p) => p.id === activeId) ?? pivotPresets[0];
50  table = new SimpleTableVanilla(tableHost, {
51    defaultHeaders: pivotDemoConfig.headers,
52    rows: pivotDemoConfig.rows,
53    pivot: active.pivot,
54    columnResizing: true,
55    expandAll: active.pivot.rows.length > 1,
56    height: options?.height ?? "400px",
57    selectableCells: true,
58    theme: options?.theme,
59  });
60  return table;
61}
62
63
64// pivot.demo-data.ts
65import type { HeaderObject, PivotConfig, Row } from "simple-table-core";
66
67export const pivotHeaders: HeaderObject[] = [
68  { accessor: "region", label: "Region", width: 110, type: "string" },
69  { accessor: "country", label: "Country", width: 100, type: "string" },
70  { accessor: "category", label: "Category", width: 110, type: "string" },
71  { accessor: "product", label: "Product", width: 120, type: "string" },
72  { accessor: "channel", label: "Channel", width: 100, type: "string" },
73  { accessor: "year", label: "Year", width: 80, type: "number" },
74  { accessor: "quarter", label: "Quarter", width: 80, type: "string" },
75  {
76    accessor: "sales",
77    label: "Sales",
78    width: 100,
79    type: "number",
80    align: "right",
81    valueFormatter: ({ value }) =>
82      typeof value === "number" ? `$${value.toLocaleString()}` : "",
83  },
84  {
85    accessor: "units",
86    label: "Units",
87    width: 80,
88    type: "number",
89    align: "right",
90  },
91  {
92    accessor: "cost",
93    label: "Cost",
94    width: 100,
95    type: "number",
96    align: "right",
97    valueFormatter: ({ value }) =>
98      typeof value === "number" ? `$${value.toLocaleString()}` : "",
99  },
100];
101
102const REGIONS = ["West", "East", "North", "South"] as const;
103const COUNTRIES: Record<(typeof REGIONS)[number], string[]> = {
104  West: ["USA", "Canada"],
105  East: ["USA", "UK"],
106  North: ["Canada", "Sweden"],
107  South: ["Brazil", "Australia"],
108};
109const CATEGORIES = ["Hardware", "Software"] as const;
110const PRODUCTS: Record<(typeof CATEGORIES)[number], string[]> = {
111  Hardware: ["Widget", "Gadget", "Sensor"],
112  Software: ["License", "Subscription"],
113};
114const CHANNELS = ["Direct", "Partner", "Online"] as const;
115const YEARS = [2024, 2025] as const;
116const QUARTERS = ["Q1", "Q2", "Q3", "Q4"] as const;
117
118/** Sparse multi-dimension fact cube (~150–250 rows). */
119export function generatePivotRows(): Row[] {
120  const rows: Row[] = [];
121  let id = 1;
122  for (const region of REGIONS) {
123    for (const country of COUNTRIES[region]) {
124      for (const category of CATEGORIES) {
125        for (const product of PRODUCTS[category]) {
126          for (const channel of CHANNELS) {
127            for (const year of YEARS) {
128              for (const quarter of QUARTERS) {
129                if ((id + year + quarter.charCodeAt(1) + channel.length) % 5 !== 0) {
130                  id++;
131                  continue;
132                }
133                const base = 40 + ((id * 17) % 90);
134                rows.push({
135                  id: `r${id}`,
136                  region,
137                  country,
138                  category,
139                  product,
140                  channel,
141                  year,
142                  quarter,
143                  sales: base * 100,
144                  units: base,
145                  cost: Math.round(base * 55),
146                });
147                id++;
148              }
149            }
150          }
151        }
152      }
153    }
154  }
155  return rows;
156}
157
158export const pivotRows: Row[] = generatePivotRows();
159
160export type PivotPreset = {
161  id: string;
162  label: string;
163  pivot: PivotConfig;
164};
165
166export const pivotPresets: PivotPreset[] = [
167  {
168    id: "region-quarter",
169    label: "Region × Quarter",
170    pivot: {
171      rows: ["region"],
172      columns: ["quarter"],
173      values: [{ accessor: "sales", aggregation: { type: "sum" } }],
174    },
175  },
176  {
177    id: "nested-rows",
178    label: "Region → Product",
179    pivot: {
180      rows: ["region", "product"],
181      columns: ["quarter"],
182      values: [{ accessor: "sales", aggregation: { type: "sum" } }],
183    },
184  },
185  {
186    id: "category-year-quarter",
187    label: "Category × Year → Quarter",
188    pivot: {
189      rows: ["category"],
190      columns: ["year", "quarter"],
191      values: [{ accessor: "sales", aggregation: { type: "sum" } }],
192    },
193  },
194  {
195    id: "channel-quarter",
196    label: "Channel × Quarter",
197    pivot: {
198      rows: ["channel"],
199      columns: ["quarter"],
200      values: [
201        { accessor: "sales", aggregation: { type: "sum" }, label: "Sales" },
202        { accessor: "units", aggregation: { type: "sum" }, label: "Units" },
203      ],
204    },
205  },
206  {
207    id: "country-category",
208    label: "Country × Category",
209    pivot: {
210      rows: ["country"],
211      columns: ["category"],
212      values: [{ accessor: "sales", aggregation: { type: "average" } }],
213      showColumnTotals: false,
214    },
215  },
216  {
217    id: "values-only",
218    label: "Values only",
219    pivot: {
220      rows: ["region", "category"],
221      columns: [],
222      values: [
223        { accessor: "sales", aggregation: { type: "sum" } },
224        { accessor: "cost", aggregation: { type: "sum" } },
225      ],
226    },
227  },
228];
229
230export const pivotConfig: PivotConfig = pivotPresets[0].pivot;
231
232export const pivotDemoConfig = {
233  headers: pivotHeaders,
234  rows: pivotRows,
235  tableProps: { pivot: pivotConfig },
236  presets: pivotPresets,
237};
238