# Server-Side Row Model - Pivoting

Canonical page: https://www.adaptabletools.com/docs/dev-guide-row-models-server-pivoting

- Pivoting is available in AdapTable when using the Server-Side Row Model
- The data needs to be pivoted on the server and passed to the client to display
- Pivot result columns are created dynamically from the server response

Client-side pivot state and ColDefs — [Defining Pivot Layouts](https://www.adaptabletools.com/docs/handbook-layouts-pivot-defining/index.md) and [Pivot Layouts](https://www.adaptabletools.com/docs/handbook-layouts-pivot/index.md). This guide covers server-side data and columns only.

AdapTable fully supports Pivoting when using the Server Side Row Model.

Nothing additional needs to be added, on the AdapTable side, to that which is provided in AG Grid's Server-Side architecture.

After each `getRows` response you typically build AG Grid **pivot result columns** from the fields the server returns (see `pivotResultColumns.ts` in the demo).

## Column Order

Each time the datasource supplies the Pivot Result Columns to AG Grid, they are displayed in the supplied order.

That order also replaces the one saved in the `PivotResultColumnsOrder` property of the Layout, so an order set by the user, or provided in Initial State, is not restored when the grid loads.

To display the Pivot Result Columns in a particular order, supply them to AG Grid in that order

**Example: SSRM - Pivoting**

Server-Side Row Model: Pivoting

- Uses the public athletes mock server (`data.adaptable.dev`) to pivot on the server and return rows + `pivotFields`
- Switch Layouts in the Dashboard toolbar to compare patterns:
  - **Year Pivot** — group by `Country`, pivot on `Year`, sum `Gold` (dynamic year columns)
  - **Multi-Value Pivot** — same pivot with `Gold` / `Silver` / `Bronze` under each year group
  - **Multi-Group Pivot** — group by `Country` then `Sport`, pivot on `Year` (expand a country to drill down)
  - **Agg-Only Pivot** — Pivot Sum style: group by `Country`, sum medals, **no** `PivotColumns` (empty array)
- Pivot result columns are rebuilt whenever the server returns a new `pivotFields` set

- Start on **Year Pivot** and note the dynamic year columns
- Switch to **Multi-Value Pivot** and expand a year header to see gold / silver / bronze
- Switch to **Multi-Group Pivot**, expand a country, and see sports as the next group level
- Switch to **Agg-Only Pivot** for medal totals with pivot mode on but no year columns

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts       [template]
  columnDefs.ts
  gridOptions.ts
  index.ts               [template]
  onAdaptableReady.ts
  pivotResultColumns.ts
  rowData.ts             [template]
  styles.css             [template]
index.html               [template]
package.json             [template]
tsconfig.json            [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {AdaptableOptions} from '@adaptabletools/adaptable';

export const adaptableOptions: AdaptableOptions = {
  primaryKey: 'id',
  adaptableId: 'SSRM - Pivoting',
  initialState: {
    Dashboard: {
      Tabs: [
        {
          Name: 'Main',
          Toolbars: ['Layout', 'SystemStatus'],
        },
      ],
    },
    Theme: {
      CurrentTheme: 'dark',
    },
    Layout: {
      CurrentLayout: 'Year Pivot',
      Layouts: [
        // Classic SSRM pivot: group → pivot → one aggregation (dynamic year columns)
        {
          Name: 'Year Pivot',
          PivotColumns: ['year'],
          PivotGroupedColumns: ['country'],
          PivotAggregationColumns: [
            {
              ColumnId: 'gold',
              AggFunc: 'sum',
            },
          ],
        },
        // Same pivot, multiple value columns → year groups with gold/silver/bronze children
        {
          Name: 'Multi-Value Pivot',
          PivotColumns: ['year'],
          PivotGroupedColumns: ['country'],
          PivotAggregationColumns: [
            {ColumnId: 'gold', AggFunc: 'sum'},
            {ColumnId: 'silver', AggFunc: 'sum'},
            {ColumnId: 'bronze', AggFunc: 'sum'},
          ],
        },
        // Drill-down row groups + year pivot (expand a country to see sports)
        {
          Name: 'Multi-Group Pivot',
          PivotColumns: ['year'],
          PivotGroupedColumns: ['country', 'sport'],
          RowGroupDisplayType: 'multi',
          PivotAggregationColumns: [
            {
              ColumnId: 'gold',
              AggFunc: 'sum',
            },
          ],
        },
        // Pivot Sum style: group + aggregations, no pivot columns (empty PivotColumns).
        // Equivalent of handbook "Pivot Sum Layout" — values show as Gold/Silver/Bronze.
        {
          Name: 'Agg-Only Pivot',
          PivotColumns: [],
          PivotGroupedColumns: ['country'],
          SuppressAggFuncInHeader: true,
          PivotAggregationColumns: [
            {ColumnId: 'gold', AggFunc: 'sum'},
            {ColumnId: 'silver', AggFunc: 'sum'},
            {ColumnId: 'bronze', AggFunc: 'sum'},
          ],
          ColumnSizing: {
            gold: {Width: 100},
            silver: {Width: 100},
            bronze: {Width: 100},
          },
        },
      ],
    },
  },
};
```

**`src/onAdaptableReady.ts`**

```ts
import {AdaptableReadyInfo, AdaptableApi, Layout} from '@adaptabletools/adaptable';
import {
  GridApi,
  IServerSideDatasource,
  IServerSideGetRowsParams,
  IServerSideGetRowsRequest,
} from 'ag-grid-enterprise';
import {applyPivotResultColumns, PivotFieldEntry} from './pivotResultColumns';

/**
 * Agg-only / Pivot Sum layouts use pivot mode with an empty PivotColumns array.
 * Leftover year (etc.) secondary columns must be cleared when switching here.
 */
function hasPivotColumns(layout: Layout): boolean {
  return (
    'PivotColumns' in layout &&
    Array.isArray(layout.PivotColumns) &&
    layout.PivotColumns.length > 0
  );
}

function clearPivotResultColumnsIfPresent(agGridApi: GridApi): void {
  if (agGridApi.getPivotResultColumns() != null) {
    // Must pass null (not []): empty array keeps AG Grid in pivot-result mode.
    agGridApi.setPivotResultColumns(null);
  }
}

export const onAdaptableReady = ({
  adaptableApi,
  agGridApi,
}: AdaptableReadyInfo) => {
  // Clear secondary pivot columns as soon as we leave a layout that uses them
  // (table layout, or agg-only pivot with no PivotColumns).
  adaptableApi.eventApi.on('LayoutChanged', info => {
    if (info.actionName !== 'LAYOUT_SELECT') {
      return;
    }
    const layout = adaptableApi.layoutApi.getCurrentLayout();
    if (
      !adaptableApi.layoutApi.isCurrentLayoutPivot() ||
      !hasPivotColumns(layout)
    ) {
      clearPivotResultColumnsIfPresent(agGridApi);
    }
  });

  agGridApi.setGridOption(
    'serverSideDatasource',
    createDataSource(adaptableApi)
  );
};

const API_BASE: string = process.env.NEXT_PUBLIC_ATHLETES_API_URL!;

function rewriteAutoGroupColumn(
  node: unknown,
  groupedColumnId?: string
): unknown {
  if (!groupedColumnId || node == null || typeof node !== 'object') {
    return node;
  }
  if (Array.isArray(node)) {
    return node.map(item => rewriteAutoGroupColumn(item, groupedColumnId));
  }
  const astNode = node as {type?: string; args?: unknown[]};
  if (astNode.type === 'COL' && astNode.args?.[0] === 'ag-Grid-AutoColumn') {
    return {
      ...astNode,
      args: [groupedColumnId, ...astNode.args.slice(1)],
    };
  }
  if (!astNode.args) {
    return node;
  }
  return {
    ...astNode,
    args: astNode.args.map(arg =>
      rewriteAutoGroupColumn(arg, groupedColumnId)
    ),
  };
}

function appendExpressionAst(
  astNodes: unknown[],
  ast: unknown,
  groupedColumnId?: string
) {
  if (ast == null) {
    return;
  }
  const nodes = Array.isArray(ast) ? ast : [ast];
  for (const node of nodes) {
    astNodes.push(rewriteAutoGroupColumn(node, groupedColumnId));
  }
}

function toServerFilterFields(
  adaptableApi: AdaptableApi,
  groupedColumnId?: string
) {
  const filterState = adaptableApi.stateApi.getAdaptableFilterState();
  const astNodes: unknown[] = [];
  appendExpressionAst(astNodes, filterState.gridFilterAST, groupedColumnId);
  for (const columnFilter of filterState.columnFilters ?? []) {
    if (!columnFilter.Expression) {
      continue;
    }
    appendExpressionAst(
      astNodes,
      adaptableApi.expressionApi.getASTForExpression(columnFilter.Expression),
      groupedColumnId
    );
  }
  return {
    adaptableFilters: [],
    gridFilterAST: astNodes.length ? astNodes : undefined,
  };
}

function createDataSource(adaptableApi: AdaptableApi): IServerSideDatasource {
  return {
    getRows(params: IServerSideGetRowsParams) {
      const getRowsRequest: IServerSideGetRowsRequest = params.request;
      const adaptableSortState = adaptableApi.stateApi.getAdaptableSortState();
      const groupedColumn = getRowsRequest.rowGroupCols?.[0];
      const serverFilters = toServerFilterFields(
        adaptableApi,
        groupedColumn?.field ?? groupedColumn?.id
      );

      const sortModel = getRowsRequest.sortModel.map(sort => {
        const customSort = adaptableSortState.customSorts.find(
          customSort => customSort.ColumnId === sort.colId
        );
        if (customSort) {
          return {
            ...sort,
            sortedValues: customSort.SortedValues,
          };
        }
        return sort;
      });

      const request = {
        ...getRowsRequest,
        sortModel,
        ...serverFilters,
        includeSQL: true,
      };

      fetch(API_BASE, {
        method: 'post',
        body: JSON.stringify(request),
        headers: {'Content-Type': 'application/json; charset=utf-8'},
      })
        .then(httpResponse => httpResponse.json())
        .then(response => {
          params.success({
            rowData: response.rows,
            rowCount: response.lastRow ?? 0,
          });

          // Pivot layouts with pivot columns: build dynamic result columns from
          // the server response. Agg-only pivot (no pivotFields): clear leftovers
          // so Gold/Silver/Bronze value columns can show.
          const pivotFields = response.pivotFields as
            | PivotFieldEntry[]
            | undefined;
          if (pivotFields?.length) {
            applyPivotResultColumns(pivotFields, params.api);
          } else {
            clearPivotResultColumnsIfPresent(params.api);
          }

          if (response.sql) {
            adaptableApi.systemStatusApi.setInfoSystemStatus(
              `SQL: ${response.sql.slice(0, 40)}`,
              response.sql
            );
          }
        })
        .catch(() => {
          params.fail();
        });
    },
  };
}
```

**`src/pivotResultColumns.ts`**

```ts
import type {ColDef, ColGroupDef, GridApi} from 'ag-grid-enterprise';

/**
 * Shape returned by the athletes mock server for each pivoted result field.
 * Matches the showcase SSRM client (`AdaptableTools/showcase-server-side-row-model`).
 */
export interface PivotFieldEntry {
  field: string;
  pivotValues: Record<string, string | number>;
  valueColumn: string;
  aggFunc: string;
}

/**
 * Build AG Grid pivot result columns (with column groups when needed) from the
 * mock server's `pivotFields`, and apply them to the grid.
 *
 * Replaces an older demo helper that treated each field as `{ year: 2000 }` and
 * skipped updates once any result columns already existed.
 */
export function applyPivotResultColumns(
  pivotFields: PivotFieldEntry[],
  agGridApi: GridApi
): void {
  const newFieldIds = pivotFields.map(f => f.field);
  const existingCols = agGridApi.getPivotResultColumns();

  if (existingCols?.length) {
    const existingIds = existingCols.map(c => c.getColId());
    if (
      existingIds.length === newFieldIds.length &&
      existingIds.every((id, i) => id === newFieldIds[i])
    ) {
      return;
    }
  }

  agGridApi.setPivotResultColumns(createPivotResultColumns(pivotFields));
}

export function createPivotResultColumns(
  pivotFields: PivotFieldEntry[]
): (ColDef | ColGroupDef)[] {
  if (!pivotFields.length) {
    return [];
  }

  const uniqueValueCols = new Set(pivotFields.map(f => f.valueColumn));
  const hasMultipleValueCols = uniqueValueCols.size > 1;
  const pivotKeys = Object.keys(pivotFields[0].pivotValues);
  const root: (ColDef | ColGroupDef)[] = [];

  for (const pf of pivotFields) {
    const allLevels = pivotKeys.map(k => String(pf.pivotValues[k]));

    if (hasMultipleValueCols) {
      // e.g. Year → gold / silver / bronze under each year group
      insertIntoTree(root, allLevels, {
        colId: pf.field,
        headerName: pf.valueColumn,
        field: pf.field,
        filter: false,
        cellDataType: 'number',
      });
    } else {
      // Single value column: leaf header is the pivot value (e.g. "2008")
      const groupLevels = allLevels.slice(0, -1);
      const leafLabel = allLevels[allLevels.length - 1];
      insertIntoTree(root, groupLevels, {
        colId: pf.field,
        headerName: leafLabel,
        field: pf.field,
        filter: false,
        cellDataType: 'number',
      });
    }
  }

  return root;
}

function insertIntoTree(
  siblings: (ColDef | ColGroupDef)[],
  groupLevels: string[],
  leafCol: ColDef,
  parentPath = ''
): void {
  if (groupLevels.length === 0) {
    siblings.push(leafCol);
    return;
  }

  const [current, ...rest] = groupLevels;
  const groupId = parentPath ? `${parentPath}_${current}` : `pivot_${current}`;

  let group = siblings.find(
    (s): s is ColGroupDef => 'groupId' in s && s.groupId === groupId
  );

  if (!group) {
    group = {groupId, headerName: current, children: []};
    siblings.push(group);
  }

  insertIntoTree(group.children!, rest, leafCol, groupId);
}
```

**`src/gridOptions.ts`**

```ts
import {GridOptions, themeBalham} from 'ag-grid-enterprise';

import {columnDefs} from './columnDefs';

export const gridOptions: GridOptions = {
  theme: themeBalham,
  defaultColDef: {
    sortable: true,
    filter: true,
    floatingFilter: true,
    enableRowGroup: true,
  },
  rowModelType: 'serverSide',
  columnDefs: columnDefs,
  sideBar: ['adaptable', 'columns', 'filters'],
};
```

**`src/columnDefs.ts`**

```ts
import {ColDef} from 'ag-grid-enterprise';

export const columnDefs: ColDef[] = [
  {field: 'id', hide: true, cellDataType: 'text'},
  {
    field: 'athlete',
    cellDataType: 'text',
    enableRowGroup: true,
    resizable: true,
  },
  {
    field: 'country',
    cellDataType: 'text',
    enableRowGroup: true,
    resizable: true,
  },
  {
    field: 'sport',
    cellDataType: 'text',
    enableRowGroup: true,
    enablePivot: true,
    resizable: true,
  },
  {
    field: 'year',
    cellDataType: 'number',
    enableRowGroup: true,
    enablePivot: true,
    resizable: true,
  },
  {
    field: 'gold',
    aggFunc: 'sum',
    cellDataType: 'number',
    enableValue: true,
    resizable: true,
  },
  {
    field: 'silver',
    aggFunc: 'sum',
    cellDataType: 'number',
    enableValue: true,
    resizable: true,
  },
  {
    field: 'bronze',
    aggFunc: 'sum',
    cellDataType: 'number',
    enableValue: true,
    resizable: true,
  },
];
```
