Lattice Grid Buy a licence

demo D72

Pivot in htmx

Transposing values across distinct keys, with a column guard

columns.pivot(['status'])

This grid transposes records so distinct values in a chosen field become the columns, with each cell holding the aggregated figure for that crossing. It answers 'how much of this by that', revenue by product and quarter say, from a flat list without a spreadsheet.

Building…
Loading a live grid…

This is the htmx version. A server-rendered table is upgraded in place, so the page is readable before any script runs and gains the full grid when the module loads, which suits a progressively enhanced app.

The configuration

<link rel="stylesheet" href="https://cdn.latticegrid.dev/lattice-grid@1.107.0/lattice-grid.min.css">

<!-- A real, server-rendered table: readable with JavaScript off. data-field on
     each header names the column that cell becomes, and data-id on each row is
     the key the grid carries it under. -->
<table id="pivot-table" data-lattice-grid>
  <thead>
    <tr>
      <th data-field="account">Account</th>
      <th data-field="environment">Env</th>
      <th data-field="cost">Monthly cost</th>
    </tr>
  </thead>
  <tbody>
    <tr data-id="1"><td>Legacy (Do Not Decommission)</td><td>staging</td><td>31612.55</td></tr>
    <tr data-id="2"><td>Cost Optimisation Programme</td><td>prod</td><td>6109.09</td></tr>
    <tr data-id="3"><td>Data &amp; Insights</td><td>dev</td><td>42.69</td></tr>
    <!-- the rest of the rows, rendered by your server -->
  </tbody>
</table>

<script type="module">
  import { hydrateTable } from 'https://cdn.latticegrid.dev/lattice-grid@1.107.0/modules/htmx.esm.min.js';

  // The markup carries the rows; a server-rendered table's column types are
  // declared in the config hydrateTable takes. The grid replaces the table
  // where it stood.
  const grid = hydrateTable(document.getElementById('pivot-table'), {
    // A cross-tab: accounts run down the side, one generated column per
    // environment runs across the top, and each cell holds the summed cost at
    // that crossing. maxColumns is the guard: an axis with too many distinct
    // values shows the rows unpivoted rather than a grid nobody can read.
    pivot: { enabled: true, maxColumns: 24 },
    // With a single value column each generated heading is titled with its
    // environment alone.
    showTotalInHeader: true,
    toolPanel: {
      side: 'left',
      panels: ['columns', 'filters', 'views', 'quick'],
      actions: ['undo', 'redo', 'export', 'restore', 'maximise'],
      exportName: 'lattice-demo',
    },
    columns: [
      { field: 'account', title: 'Account', filter: { type: 'set' }, group: { enabled: true, index: 0 } },
      { field: 'environment', title: 'Env', filter: { type: 'set' }, pivot: { enabled: true, index: 0 } },
      { field: 'cost', title: 'Monthly cost', type: 'number', layout: { width: 170 },
        format: { style: 'currency', currency: 'USD', decimals: 2 }, total: 'sum' },
    ],
  });
</script>

Turning row values into columns with a pivot table

A pivot transposes data: instead of one row per record, distinct values in a chosen field become the columns themselves, and each cell holds the aggregated figure for that combination, such as revenue by product down the rows and by quarter across the top. A developer reaches for this once a flat table stops answering the question directly, typically when a stakeholder wants a cross-tabulation rather than a list, or when a report needs the same underlying rows read two ways without a second dataset. This demo builds that structure through columns.pivot(['status']), an array of field names whose distinct values become the pivoted column set, with a guard that caps the number of generated columns so a field with high cardinality cannot silently produce hundreds of them. Because Lattice Grid is a JavaScript data grid built on virtual scrolling and incremental aggregation rather than server round trips, the pivot recalculates from the same per-column total definitions used elsewhere in the grid, so a sum defined once for grouping or the grand total row is reused here without a separate formula. Pivoted headers carry the same column semantics as any other, including sort and resize, so the transposed view behaves like a normal grid rather than a static export.

How do I create a pivot table in a JavaScript data grid?

Call columns.pivot() with an array of field names whose values should become columns, for example columns.pivot(['status']) to spread each distinct status into its own column. Lattice Grid aggregates the existing per-column totals into each generated cell and applies a column guard that limits how many columns a high-cardinality field can produce. See the step-by-step how to pivot data in a grid guide for a runnable example and the source on GitHub.

Related demos