Pivot

<script lang="ts">
  import { createDataGrid, DataGrid, pivot, type ColumnDef } from '@sv5ui/datagrid';
  // The rows, and two more from a quarter the first list has never held.
  import { sales, fourthQuarter } from './sales';

  interface Sale {
    id: number;
    region: string;
    quarter: string;
    product: string;
    amount: number;
  }

  // Every field a pivot names is a column id, so the columns are declared
  // as they would be for a flat grid.
  const columns: ColumnDef<Sale>[] = [
    // Pinned, so the row labels stay in view when a wide crossing scrolls.
    { id: 'region', header: 'Region', pinned: 'left' },
    { id: 'quarter', header: 'Quarter' },
    { id: 'product', header: 'Product' },
    { id: 'amount', header: 'Amount', type: 'currency' }
  ];

  const grid = createDataGrid<Sale>({
    columns,
    data: sales,
    getRowId: (sale) => String(sale.id),
    features: [
      // The factory does not infer the row type, so name it.
      pivot<Sale>({
        rows: ['region'],
        columns: ['quarter'],
        values: [{ field: 'amount', agg: 'sum' }]
      })
    ]
  });

  // A quarter the data has not held before arrives as a column. Hand the
  // grid a new array and nothing else changes.
  function addQ4() {
    grid.data = [...grid.data, ...fourthQuarter];
  }
</script>

<DataGrid {grid} />

// North has three rows: 100 and 80 in Q1, 150 in Q2.
pivot<Sale>({
  rows: ['region'],
  columns: ['quarter'],
  values: [{ field: 'amount', agg: 'avg' }],
  totalColumn: 'All quarters'
});

//            Q1     Q2    All quarters
//   North    90    150        110
//
// 110 is the average of the three rows. The average of the two cells
// beside it is 120, and that number is wrong: it has forgotten that Q1
// stood on two rows and Q2 on one.

pivot<Sale>({
  rows: ['region'],
  // Two column fields nest: products inside each quarter.
  columns: ['quarter', 'product'],
  values: [{ field: 'amount', agg: 'sum' }],

  // A column closing the crossing, headed by this text.
  totalColumn: 'Total',

  // A row closing the page, headed by this text in the first row field.
  totalRow: 'All regions',

  // A column closing each group of the column axis. It is handed the
  // group's own header and answers the subtotal's.
  columnSubtotal: (header) => `${header} total`
});

// With one column field there are no groups, so columnSubtotal draws
// nothing: every generated column is already a leaf.

import { filtering, getFiltering, pivot } from '@sv5ui/datagrid';

const grid = createDataGrid<Sale>({
  columns,
  data: sales,
  getRowId,
  features: [
    filtering<Sale>(),
    pivot<Sale>({
      rows: ['region'],
      columns: ['quarter'],
      values: [{ field: 'amount', agg: 'sum' }],
      totalColumn: 'Total'
    })
  ]
});

// product is on neither axis, so it is not drawn. It is hidden rather
// than dropped, and a hidden column still filters.
getFiltering(grid)?.setColumnFilter('product', { kind: 'set', values: ['Gadget'] });

// The quarters come from the data before any filter, so a quarter the
// filter emptied keeps its column and draws empty cells.

const grid = createDataGrid<Sale>({
  columns,
  data: sales,
  getRowId,
  features: [
    pivot<Sale>({
      rows: ['region'],
      columns: ['quarter'],
      values: [{ field: 'amount', agg: 'sum' }]
    })
  ]
});

// Taking the last field off an axis is not an error. It turns the
// crossing off, and the grid draws its rows as a flat grid would.
grid.api.removePivotField?.('quarter');
grid.api.isPivoting?.(); // false

// Putting a field back turns it on again.
grid.api.addPivotColumnField?.('quarter');

// clearPivot empties both axes and leaves the measures where they were,
// so the next field you add crosses the same values.
grid.api.clearPivot?.();

// The methods are optional on grid.api because only a grid that
// registered pivot() has them. getPivot(grid) is the same thing typed:
import { getPivot } from '@sv5ui/datagrid';

const state = getPivot(grid);
state?.isCrossing;
state?.addRowField('product');