Server-side pivot

About this example

A Svelte 5 pivot designer that pivots on the server. Drag fields into Rows, Columns and Values; on Apply the designer sends one request through the server-side row model, with Rows as groupBy, Columns as pivotBy and Values as aggregations. The backend answers with one field per pivot key and aggregation and lists them in pivotResultFields; the model turns that list into header groups per key with a value column under each, replacing the grid's columns while pivot mode lasts. Over the same million-row warehouse as the row model demo, with a grand total pinned at the bottom and a request log underneath.

The pivot designer in server mode: Rows become groupBy, Columns pivotBy, Values aggregations, and every applied layout is one request to the warehouse from demo 467 (a million rows). The backend answers with one field per (pivot key x aggregation) and lists them in pivotResultFields; the model turns that list into the column groups you see. Apply / Cancel hold a slice-and-dice session to one request.

Both the designer's server mode and the row model are Enterprise; the contract the warehouse implements is free.

Imports, features and API used

Imports: @svgrid/grid, @svgrid/enterprise, ../shared/server-warehouse

Frequently asked questions

Where does the pivoting happen?

On the server. The request carries pivotBy and pivotMode beside groupBy and aggregations; the backend splits every aggregate per pivot key path and names the fields, for example 2024_amount, and lists them in pivotResultFields. The grid never sees the underlying rows.

Why is there an Apply button?

applyMode deferred holds the wells' edits locally until Apply, so a slice-and-dice session with three drags is one request rather than three. Cancel puts the wells back to the applied layout.

Can a pivoted group be expanded?

A region opens onto its countries with the pivot repeated per country. The innermost group level does not open under pivot: its rows are the pivoted result, and there are no leaves beneath them to show.

Related documentation

• Server pivot - Enterprise

Related articles

• Inside SvGrid: The Row Model and Sorting - How sorting shaped SvGrid's row-model pipeline - the decisions made early that every later feature inherited.
• Using SvGrid with TanStack Query in Svelte - Wire TanStack Query's caching and background refetch into SvGrid for a server-driven grid that pages instantly and never shows a blank screen.
• A Svelte Data Grid with SvelteKit and Supabase - Wire SvGrid to a Supabase Postgres backend with server-side pagination, sorting, and filtering - keeping credentials on the server and queries fast with proper indexing.

Source code (468-server-pivot.svelte)

<!-- Documented in: docs/help/server/server-pivot.md -->
<script lang="ts">
  /**
   * 468. Server-side pivot
   * -----------------------
   * The pivot designer in server mode: Rows become `groupBy`, Columns
   * `pivotBy`, Values `aggregations`, and every applied layout is one request
   * to the warehouse from demo 467 (a million rows). The backend answers with
   * one field per (pivot key x aggregation) and lists them in
   * `pivotResultFields`; the model turns that list into the column groups
   * you see. Apply / Cancel hold a slice-and-dice session to one request.
   *
   * Both the designer's server mode and the row model are Enterprise; the
   * contract the warehouse implements is free.
   */
  import type { GridColumns } from '@svgrid/grid'
  import { SvPivotDesigner, createServerRowModel, setLicenseKey, type PivotField, type PivotLayout } from '@svgrid/enterprise'
  import { createWarehouse, type WarehouseLogEntry, type WarehouseRow } from '../shared/server-warehouse'

  setLicenseKey('SVENTERPRISE-DEV-LOCAL')

  let log = $state<WarehouseLogEntry[]>([])
  const warehouse = createWarehouse({
    rows: 1_000_000,
    latencyMs: [40, 120],
    onRequest: (entry) => {
      log = [entry, ...log].slice(0, 12)
    },
  })

  const server = createServerRowModel<WarehouseRow>(warehouse, {
    groupBy: ['region', 'country'],
    aggregations: [{ col: 'amount', fn: 'sum' }],
    pivotBy: ['year'],
    pivotMode: true,
    grandTotalRow: 'pinnedBottom',
    childCount: (r) => (r as { childCount?: number }).childCount,
    // Each generated value column: the measure's name, a currency format, room for it.
    pivotResultColumn: (field, def) => ({
      ...def,
      header: 'Amount',
      width: 130,
      format: { type: 'number', options: { style: 'currency', currency: 'USD', maximumFractionDigits: 0 } },
    }),
  })

  const money = { type: 'currency', currency: 'USD', options: { maximumFractionDigits: 0 } } as const
  const fields: PivotField<WarehouseRow>[] = [
    { field: 'region', label: 'Region', kind: 'dimension', group: 'Geography' },
    { field: 'country', label: 'Country', kind: 'dimension', group: 'Geography' },
    { field: 'rep', label: 'Rep', kind: 'dimension', group: 'People' },
    { field: 'category', label: 'Category', kind: 'dimension', group: 'Catalogue' },
    { field: 'product', label: 'Product', kind: 'dimension', group: 'Catalogue' },
    { field: 'status', label: 'Status', kind: 'dimension', group: 'Order' },
    { field: 'year', label: 'Year', kind: 'dimension', group: 'Time' },
    { field: 'quarter', label: 'Quarter', kind: 'dimension', group: 'Time' },
    { field: 'amount', label: 'Amount', kind: 'measure', defaultAgg: 'sum', format: money },
    { field: 'qty', label: 'Quantity', kind: 'measure', defaultAgg: 'sum', format: { type: 'number' } },
  ]
  let layout = $state<PivotLayout>({
    rows: ['region', 'country'],
    cols: ['year'],
    values: [{ field: 'amount', agg: 'sum', label: 'Amount', format: money }],
    filters: [],
  })
  let pivotMode = $state(true)

  const usd = { type: 'number' as const, options: { style: 'currency' as const, currency: 'USD', maximumFractionDigits: 0 } }
  const flatColumns: GridColumns<WarehouseRow> = [
    { field: 'region', header: 'Region', width: 120 },
    { field: 'country', header: 'Country', width: 140 },
    { field: 'product', header: 'Product', width: 130 },
    { field: 'status', header: 'Status', width: 100 },
    { field: 'qty', header: 'Qty', width: 90, align: 'right' as const },
    { field: 'amount', header: 'Amount', width: 140, align: 'right' as const, format: usd },
  ]
</script>

<section class="wrap">
  <header class="chrome">
    <span class="note">
      Drag Quarter into Columns beside Year, or Status into Rows, then Apply: one request per applied layout,
      over a million rows that never leave the server. Expand a region to see the pivot repeated per country
      beneath it; the innermost group is the result itself, so it has no expander.
    </span>
  </header>
  <div class="host">
    <SvPivotDesigner
      {server}
      {fields}
      bind:layout
      bind:pivotMode
      {flatColumns}
      groupColumn={{ header: 'Region / Country', width: 240, leafField: 'product' }}
      applyMode="deferred"
      panelPosition="right"
      gridFitColumns={false}
      columnTree
      toolTabs
    />
  </div>
  <footer class="log" aria-label="Request log">
    <span class="log-label">Requests</span>
    {#each log as e (e.seq)}
      <span class="log-item" class:is-failed={e.failed}>
        <strong>{e.kind}</strong> {e.route.length ? e.route.join(' > ') : 'root'} {e.range} <em>{e.ms} ms</em>
      </span>
    {/each}
    {#if !log.length}<span class="log-item">none yet</span>{/if}
  </footer>
</section>

<style>
  .wrap { display: flex; flex-direction: column; flex: 1; gap: 10px; height: 100%; min-height: 0; }
  .chrome { display: flex; align-items: center; gap: 12px; flex-wrap: wrap; flex: none; }
  .note { font-size: 12px; color: var(--sg-muted, #64748b); }
  .host { flex: 1; min-height: 0; }
  .log {
    display: flex;
    gap: 12px;
    flex: none;
    overflow: auto;
    white-space: nowrap;
    font-size: 12px;
    color: var(--sg-muted, #64748b);
    font-variant-numeric: tabular-nums;
    padding: 2px 0;
  }
  .log-label { font-weight: 600; color: var(--sg-fg, #0f172a); }
  .log-item strong { color: var(--sg-fg, #0f172a); text-transform: uppercase; font-size: 10.5px; }
  .log-item em { font-style: normal; color: var(--sg-fg, #0f172a); }
  .log-item.is-failed { color: var(--sg-danger, #b91c1c); }
</style>

View this example on GitHub

More Server-Side Row Model examples

• Server-Side Row Model: 1,000,000 rows - One grid, one rowModel prop, a million rows that stay on the server. Sort, filter, global search, grouping to any depth (Region > Country > Rep), infinite scroll or paging, inline edits applied back as transactions with the subtotal following, add and delete, select-all across rows the grid never loaded with a bulk edit by rule, failed blocks with Retry, and a request log that shows every call to the columnar warehouse behind it. The row model ships in @svgrid/enterprise; the datasource contract is free.
• Server grouping (row model) - Server-side grouping through one getRows contract: the request carries groupBy + groupKeys, and createServerRowModel owns the group tree - a block cache per level, lazy expand, per-group sums and a subtotal footer, race-safety - mounted through the one rowModel prop. Leaves arrive by scroll, behind a Load N more row, or paged across the whole tree, and the group panel regroups on the fly. Here a 63,000-row in-memory server behind 200ms latency; the grid holds only the groups you expand. The row model ships in @svgrid/enterprise.
• Server tree data (row model) - A file tree the grid never holds whole: expanding a folder is one getRows with the folder path as groupKeys, answered with that folder's entries one block at a time. createServerRowModel in treeData mode owns the lazy expand, a block cache per folder, open-by-default, expand and collapse all, a per-folder refresh that re-reads one folder in place, and transactions that add or delete a file without a refetch. The server generates each folder from a seeded PRNG on first request, five levels deep.
• Server transactions (live feed) - A socket-style feed of changes the server already made, applied without a refetch: a price tick patches the loaded row in place with a flash (updateRowData), a new order lands at the top of its warehouse and a shipped one leaves (applyTransactionAsync, batched every 500 ms, addressed by route). Every result carries a status the log shows: applied, cancelled under the veto hook, storeNotFound for a warehouse whose level is not cached. Refresh totals recomputes the sums a transaction leaves alone.
• Server selection: select all, minus these - The header checkbox selects every row the filter matches, loaded or not, and the selection becomes a rule: all except these ids, or per group under grouping. The panel shows getSelectionState() live, Save and Restore round-trip it, and a bulk action sends the rule to the server as one updateWhere that answers with the count it changed. The selection bar shows the server's number, not the ticks on screen.