# Pivoting

Canonical page: https://infinite-table.com/docs/learn/grouping-and-pivoting/pivoting/overview

An enteprise-level feature `InfiniteTable` provides is the pivoting functionality. Combined with grouping and advanced aggregation, it unlocks new ways to visualize data.

Pivoting is first defined at the `DataSource` level, via the [`pivotBy`](https://infinite-table.com/docs/reference/infinite-table-props.md#pivotBy) prop. It's an array of objects, each with a `field` property bound (so `pivotBy[].field` is keyof `DATA_TYPE`) to the `DataSource`.

Pivoting generates columns based on the pivoting values, so you have to pass those generated columns into the `<InfiniteTable />` component.

You do that by using a `function` as a direct child of the `DataSource`, and in that function you have access to the generated `pivotColumns` array. Likewise for `pivotColumnGroups`.

For more pivoting examples, see [our pivoting demos](https://infinite-table.com/docs/learn/examples/dynamic-pivoting-example.md)

```ts
const pivotBy = [{ field: 'team' }]
 // field needs to be keyof DATA_TYPE both in `pivotBy` and `groupBy`
const groupBy = [{field: 'department'}, {field: 'country'}]

<DataSource<DATA_TYPE> pivotBy={pivotBy} groupBy={groupBy}>
{ ({pivotColumns, pivotColumnGroups}) => {
  return <InfiniteTable<DATA_TYPE>
    pivotColumns={pivotColumns}
    pivotColumnGroups={pivotColumnGroups}
  />
} }
</DataSource>
```

**Example: Pivoting with avg aggregation**

```ts
import {
  InfiniteTable,
  DataSource,
  GroupRowsState,
} from '@infinite-table/infinite-react';
import type {
  InfiniteTableColumnAggregator,
  InfiniteTablePropColumns,
  DataSourcePropAggregationReducers,
  DataSourceGroupBy,
  DataSourcePivotBy,
} from '@infinite-table/infinite-react';
import * as React from 'react';

type Developer = {
  id: number;
  firstName: string;
  lastName: string;
  country: string;
  city: string;
  currency: string;
  preferredLanguage: string;
  stack: string;
  canDesign: 'yes' | 'no';
  hobby: string;
  salary: number;
  age: number;
};

const dataSource = () => {
  return fetch(process.env.NEXT_PUBLIC_BASE_URL + '/developers100')
    .then((r) => r.json())
    .then((data: Developer[]) => data)
    .then(
      (data) =>
        new Promise<Developer[]>((resolve) => {
          setTimeout(() => resolve(data), 1000);
        }),
    );
};

const avgReducer: InfiniteTableColumnAggregator<Developer, any> = {
  initialValue: 0,
  field: 'salary',
  reducer: (acc, sum) => acc + sum,
  done: (sum, arr) => (arr.length ? sum / arr.length : 0),
};

const reducers: DataSourcePropAggregationReducers<Developer> = {
  salary: avgReducer,
};

const columns: InfiniteTablePropColumns<Developer> = {
  id: { field: 'id' },
  firstName: { field: 'firstName' },
  preferredLanguage: { field: 'preferredLanguage' },
  stack: { field: 'stack' },
  country: { field: 'country' },
  canDesign: { field: 'canDesign' },
  hobby: { field: 'hobby' },
  city: { field: 'city' },
  age: { field: 'age' },
  salary: { field: 'salary', type: 'number' },
  currency: { field: 'currency' },
};

const groupRowsState = new GroupRowsState({
  expandedRows: [],
  collapsedRows: true,
});

export default function GroupByExample() {
  const groupBy: DataSourceGroupBy<Developer>[] = React.useMemo(
    () => [
      {
        field: 'preferredLanguage',
      },
      { field: 'stack' },
    ],
    [],
  );

  const pivotBy: DataSourcePivotBy<Developer>[] = React.useMemo(
    () => [
      { field: 'country' },
      {
        field: 'canDesign',
      },
    ],
    [],
  );

  return (
    <>
      <DataSource<Developer>
        primaryKey="id"
        data={dataSource}
        groupBy={groupBy}
        pivotBy={pivotBy}
        aggregationReducers={reducers}
        defaultGroupRowsState={groupRowsState}
      >
        {({ pivotColumns, pivotColumnGroups }) => {
          return (
            <InfiniteTable<Developer>
              debugId="pivoting-example"
              columns={columns}
              pivotColumns={pivotColumns}
              pivotColumnGroups={pivotColumnGroups}
              columnDefaultWidth={200}
              pivotTotalColumnPosition="end"
            />
          );
        }}
      </DataSource>
    </>
  );
}
```

## Customizing Pivot Columns

There are a number of ways to customize the pivot columns and [pivot column groups](https://infinite-table.com/docs/reference/datasource-props/index.md#pivotBy.columnGroup). This is something you generally want to do, as they are generated and you might need to tweak column headers, size, etc.

The default behavior for pivot columns generated for aggregations is that they inherit the properties of the original columns bound to the same field as the aggregation.

```ts
const avgReducer: InfiniteTableColumnAggregator<Developer, any> = {
  initialValue: 0,
  reducer: (acc, sum) => acc + sum,
  done: (sum, arr) => {
    return Math.floor(arr.length ? sum / arr.length : 0);
  },
};
const aggregationReducers: DataSourceProps<Developer>['aggregationReducers'] = {
  // will have the same configuration as the `salary` column
  avgSalary: { field: 'salary', ...avgReducer },
  avgAge: {
    field: 'age',
    ...avgReducer,
    pivotColumn: {
      // will have the same configuration as the `preferredLanguage` column
      inheritFromColumn: 'preferredLanguage',
      // but specify a custom default width
      defaultWidth: 500,
    },
  },
};
```

**Example: Pivot columns inherit from original columns bound to the same field**

```ts
import {
  InfiniteTable,
  DataSource,
  GroupRowsState,
} from '@infinite-table/infinite-react';
import type {
  DataSourcePropAggregationReducers,
  InfiniteTableColumnAggregator,
  InfiniteTablePropColumns,
  DataSourceGroupBy,
  DataSourcePivotBy,
} from '@infinite-table/infinite-react';
import * as React from 'react';

type Developer = {
  id: number;
  firstName: string;
  lastName: string;
  country: string;
  city: string;
  currency: string;
  preferredLanguage: string;
  stack: string;
  canDesign: 'yes' | 'no';
  hobby: string;
  salary: number;
  age: number;
};

const dataSource = () => {
  return fetch(process.env.NEXT_PUBLIC_BASE_URL + '/developers100')
    .then((r) => r.json())
    .then((data: Developer[]) => data);
};

const avgReducer: InfiniteTableColumnAggregator<Developer, any> = {
  initialValue: 0,
  reducer: (acc, sum) => acc + sum,
  done: (sum, arr) => (arr.length ? sum / arr.length : 0),
};

const columnAggregations: DataSourcePropAggregationReducers<Developer> = {
  avgSalary: {
    field: 'salary',
    name: 'Average salary',
    ...avgReducer,
  },
  avgAge: {
    field: 'age',
    ...avgReducer,
    pivotColumn: {
      defaultWidth: 500,
      inheritFromColumn: 'firstName',
    },
  },
};

const columns: InfiniteTablePropColumns<Developer> = {
  id: { field: 'id' },
  firstName: {
    field: 'firstName',
    style: {
      fontWeight: 'bold',
    },
    renderValue: ({ value }) => <>{value}!</>,
  },
  preferredLanguage: { field: 'preferredLanguage' },
  stack: { field: 'stack' },
  country: { field: 'country' },
  canDesign: { field: 'canDesign' },
  hobby: { field: 'hobby' },

  city: { field: 'city' },
  age: { field: 'age' },
  salary: {
    field: 'salary',
    type: 'number',
    header: 'Salary',
    style: { color: 'red' },
  },
  currency: { field: 'currency' },
};

const groupRowsState = new GroupRowsState({
  expandedRows: [],
  collapsedRows: true,
});

export default function PivotByExample() {
  const groupBy: DataSourceGroupBy<Developer>[] = React.useMemo(
    () => [
      {
        field: 'preferredLanguage',
      },
      { field: 'stack' },
    ],
    [],
  );

  const pivotBy: DataSourcePivotBy<Developer>[] = React.useMemo(
    () => [
      { field: 'country' },
      {
        field: 'canDesign',
      },
    ],
    [],
  );

  return (
    <>
      <DataSource<Developer>
        primaryKey="id"
        data={dataSource}
        groupBy={groupBy}
        pivotBy={pivotBy}
        aggregationReducers={columnAggregations}
        defaultGroupRowsState={groupRowsState}
      >
        {({ pivotColumns, pivotColumnGroups }) => {
          return (
            <InfiniteTable<Developer>
              debugId="pivot-column-inherit-example"
              columns={columns}
              hideEmptyGroupColumns
              pivotColumns={pivotColumns}
              pivotColumnGroups={pivotColumnGroups}
              columnDefaultWidth={180}
            />
          );
        }}
      </DataSource>
    </>
  );
}
```

Another way to do it is to specify [`pivotBy.column`](https://infinite-table.com/docs/reference/datasource-props/index.md#pivotBy.column), as either an object, or (more importantly) as a function.
If you pass an object, it will be applied to all pivot columns in the column group generated for the `field` property.

```tsx
const pivotBy: DataSourcePivotBy<DATA_TYPE>[] = [
  { field: 'country' },
  { field: 'canDesign', column: { defaultWidth: 400 } },
];

<DataSource pivotBy={pivotBy} />;
```

In the above example, the `column.defaultWidth=400` will be applied to columns generated for all `canDesign` values corresponding to each country. This is good but not good enough as you might want to customize the pivot column for every value in the pivot. You can do that by passing a function to the `pivotBy.column` property.

```tsx
const pivotBy: DataSourcePivotBy<DATA_TYPE>[] = [
  { field: 'country' },
  {
    field: 'canDesign',
    column: ({ column }) => {
      return {
        header: column.pivotGroupKey === 'yes' ? 'Designer' : 'Not a Designer',
      };
    },
  },
];
```

**Example: Pivoting with customized pivot column**

```ts
import {
  InfiniteTable,
  DataSource,
  GroupRowsState,
} from '@infinite-table/infinite-react';
import type {
  DataSourcePropAggregationReducers,
  InfiniteTableColumnAggregator,
  InfiniteTablePropColumns,
  DataSourceGroupBy,
  DataSourcePivotBy,
} from '@infinite-table/infinite-react';
import * as React from 'react';

type Developer = {
  id: number;
  firstName: string;
  lastName: string;
  country: string;
  city: string;
  currency: string;
  preferredLanguage: string;
  stack: string;
  canDesign: 'yes' | 'no';
  hobby: string;
  salary: number;
  age: number;
};

const dataSource = () => {
  return fetch(process.env.NEXT_PUBLIC_BASE_URL + '/developers100')
    .then((r) => r.json())
    .then((data: Developer[]) => data);
};

const avgReducer: InfiniteTableColumnAggregator<Developer, any> = {
  initialValue: 0,
  field: 'salary',
  reducer: (acc, sum) => acc + sum,
  done: (sum, arr) => (arr.length ? sum / arr.length : 0),
};

const columnAggregations: DataSourcePropAggregationReducers<Developer> = {
  salary: avgReducer,
};

const columns: InfiniteTablePropColumns<Developer> = {
  id: { field: 'id' },
  firstName: { field: 'firstName' },
  preferredLanguage: { field: 'preferredLanguage' },
  stack: { field: 'stack' },
  country: { field: 'country' },
  canDesign: { field: 'canDesign' },
  hobby: { field: 'hobby' },

  city: { field: 'city' },
  age: { field: 'age' },
  salary: { field: 'salary', type: 'number' },
  currency: { field: 'currency' },
};

const groupRowsState = new GroupRowsState({
  expandedRows: [],
  collapsedRows: true,
});

export default function PivotByExample() {
  const groupBy: DataSourceGroupBy<Developer>[] = React.useMemo(
    () => [
      {
        field: 'preferredLanguage',
      },
      { field: 'stack' },
    ],
    [],
  );

  const pivotBy: DataSourcePivotBy<Developer>[] = React.useMemo(
    () => [
      { field: 'country' },
      {
        field: 'canDesign',
        column: ({ column: pivotCol }) => {
          const lastKey =
            pivotCol.pivotGroupKeys[pivotCol.pivotGroupKeys.length - 1];

          return {
            header: lastKey === 'yes' ? '💅 Designer' : '💻 Non-designer',
          };
        },
      },
    ],
    [],
  );

  return (
    <>
      <DataSource<Developer>
        primaryKey="id"
        data={dataSource}
        groupBy={groupBy}
        pivotBy={pivotBy}
        aggregationReducers={columnAggregations}
        defaultGroupRowsState={groupRowsState}
      >
        {({ pivotColumns, pivotColumnGroups }) => {
          return (
            <InfiniteTable<Developer>
              debugId="pivoting-customize-column-example"
              columns={columns}
              hideEmptyGroupColumns
              pivotColumns={pivotColumns}
              pivotColumnGroups={pivotColumnGroups}
              columnDefaultWidth={180}
            />
          );
        }}
      </DataSource>
    </>
  );
}
```

## Total and grand-total columns

In [pivot mode](https://infinite-table.com/docs/reference/datasource-props/index.md#pivotBy) you can configure both [total columns](https://infinite-table.com/docs/reference/infinite-table-props.md#pivotTotalColumnPosition) and [grand-total columns](https://infinite-table.com/docs/reference/infinite-table-props.md#pivotGrandTotalColumnPosition). By default, grand-total columns are not displayed, so you have to explicitly set the [`pivotGrandTotalColumnPosition`](https://infinite-table.com/docs/reference/infinite-table-props.md#pivotGrandTotalColumnPosition) prop for them to be visible.

 
Pivot total columns are only displayed when [pivotBy](https://infinite-table.com/docs/reference/datasource-props/index.md#pivotBy) has two or more fields (`pivotBy.length > 1`). With a single pivot field only grand-total columns can be shown, and naving [`pivotTotalColumnPosition`](https://infinite-table.com/docs/reference/infinite-table-props.md#pivotTotalColumnPosition) has no effect  — the totals would be the same as the already displayed values.

The example below pivots by `stack` and `canDesign` so both total and grand-total columns are visible.

**Example: Pivoting with customized position for totals and grand-total columns**

```ts
import {
  InfiniteTable,
  DataSource,
  DataSourceGroupBy,
  DataSourcePivotBy,
  InfiniteTableColumnAggregator,
  DataSourcePropAggregationReducers,
} from '@infinite-table/infinite-react';
import type { InfiniteTablePropColumns } from '@infinite-table/infinite-react';
import * as React from 'react';

type Developer = {
  id: number;
  firstName: string;
  lastName: string;
  country: string;
  city: string;
  currency: string;
  preferredLanguage: string;
  stack: string;
  canDesign: 'yes' | 'no';
  hobby: string;
  salary: number;
  age: number;
};

const dataSource = () => {
  return fetch(process.env.NEXT_PUBLIC_BASE_URL + '/developers1k')
    .then((r) => r.json())
    .then((data: Developer[]) => data);
};

const columns: InfiniteTablePropColumns<Developer> = {
  id: { field: 'id', defaultWidth: 80 },
  preferredLanguage: { field: 'preferredLanguage' },
  stack: { field: 'stack' },
};

const defaultGroupBy: DataSourceGroupBy<Developer>[] = [
  {
    field: 'country',
  },
  {
    field: 'city',
  },
];

const defaultPivotBy: DataSourcePivotBy<Developer>[] = [
  {
    field: 'stack',
  },
  {
    field: 'canDesign',
    columnGroup: ({ columnGroup }) => {
      return {
        ...columnGroup,
        header:
          columnGroup.pivotGroupKey === 'yes' ? 'Designer' : 'Non-designer',
      };
    },
  },
];

const avgReducer: InfiniteTableColumnAggregator<Developer, any> = {
  initialValue: 0,

  reducer: (acc, sum) => acc + sum,
  done: (sum, arr) => Math.round(arr.length ? sum / arr.length : 0),
};

const aggregations: DataSourcePropAggregationReducers<Developer> = {
  salary: {
    ...avgReducer,
    name: 'Salary (avg)',
    field: 'salary',
  },
  age: {
    ...avgReducer,
    name: 'Age (avg)',
    field: 'age',
  },
};

export default function ColumnValueGetterExample() {
  return (
    <>
      <DataSource<Developer>
        primaryKey="id"
        defaultGroupBy={defaultGroupBy}
        defaultPivotBy={defaultPivotBy}
        aggregationReducers={aggregations}
        data={dataSource}
      >
        {({ pivotColumns, pivotColumnGroups }) => {
          return (
            <InfiniteTable<Developer>
              debugId="pivot-grand-total-column-position-example"
              groupRenderStrategy="single-column"
              columns={columns}
              columnDefaultWidth={200}
              pivotColumns={pivotColumns}
              pivotColumnGroups={pivotColumnGroups}
              pivotTotalColumnPosition="end"
              pivotGrandTotalColumnPosition="start"
            />
          );
        }}
      </DataSource>
    </>
  );
}
```

**What are grand-total columns?**

For each [aggregation reducer](https://infinite-table.com/docs/reference/datasource-props/index.md#aggregationReducers) specified in the `DataSource`, you can have a total column - this is what [grand-total columns](https://infinite-table.com/docs/reference/infinite-table-props.md#pivotGrandTotalColumnPosition) basically are.

## Server-side pivoting

By default, pivoting is client side. However, if you specify [DataSource.lazyLoad](https://infinite-table.com/docs/reference/datasource-props/index.md#lazyLoad) and provide a function that returns a promise for the [DataSource.data](https://infinite-table.com/docs/reference/datasource-props/index.md#data) prop, the table will use server-pivoted data.

The [DataSource.data](https://infinite-table.com/docs/reference/datasource-props/index.md#data) function is expected to return a promise that resolves to an object with the following shape:

- `totalCount` - the total number of records in the group we're pivoting on
- `data` - an array of objects that describes child groups, each object has the following properties:
  - `keys` - an array of the group keys (usually strings) that uniquely identifies the group, from the root to the current group
  - `data` - an object that describes the common properties of the group
  - `aggregations` - an object that describes the aggregations for the current group
  - `pivot` - the pivoted values and aggregations for each value. This object will have the following properties:
    - `totals` - an object with a key for each aggregation. The value is the aggregated value for the respective aggregation reducer.
    - `values` - an object keyed with the unique values for the pivot field. The values of those keys are objects with the same shape as the `pivot` top-level object, namely `totals` and `values`.

In the example below, let's assume the following practical scenario, with the data-type being a `Developer{country, stack, preferredLanguage, canDesign, age, salary}`.

```tsx
const groupBy = [
  { field: 'country' }, // possible values: any valid country
  { field: 'stack' }, // possible values: "backend", "frontend", "full-stack"
];
const pivotBy = [
  { field: 'preferredLanguage' }, // possible values: "TypeScript","JavaScript","Go"
  { field: 'canDesign' }, // possible values: "yes" or "no"
];

const aggregationReducers = {
  salary: { name: 'Salary (avg)', field: 'salary', reducer: 'avg' },
  age: { name: 'Age (avg)', field: 'age', reducer: 'avg' },
};
```

```tsx

const dataSource = ({ groupBy, pivotBy, groupKeys, aggregationReducers }) => {
  // make sure you return a Promise that resolves to the correct structure - see details below

  //eg: groupBy: [{ field: 'country' }, { field: 'stack' }],
  //    groupKeys: [], - so we're requesting top-level data

  //eg: groupBy: [{ field: 'country' }, { field: 'stack' }],
  //    groupKeys: ["Canada"], - so we're requesting Canada's data

  //eg: groupBy: [{ field: 'country' }, { field: 'stack' }],
  //    groupKeys: ["Canada"], - so we're requesting Canada's data

}

<DataSource lazyLoad data={dataSource}>
```

```js
{
  data: [
    {
      aggregations: {
        // for each aggregation id, have an entry
        salary: <SALARY_AGGREGATION_VALUE>,
        age: <AGE_AGGREGATION_VALUE>,
      },
      data: {
        // data is an object with the common group values
        country: "Canada"
      },
      // the array of keys that uniquely identify this group, including all parent keys
      keys: ["Canada"],
      pivot: {
        totals: {
          // for each aggregation id, have an entry
          salary: <SALARY_AGGREGATION_VALUE>,
          age: <AGE_AGGREGATION_VALUE>,
        },
        values: {
          [for each unique value]: { // eg: for country
            totals: {
              // for each aggregation, have an entry
              salary: <SALARY_AGGREGATION_VALUE>,
              age: <AGE_AGGREGATION_VALUE>,
            },
            values: {
              [for each unique value]: { // eg: for stack
                totals: {
                  salary: <SALARY_AGGREGATION_VALUE>,
                  age: <AGE_AGGREGATION_VALUE>,
                }

              }
            }
          }
        }
      }
    }
  ],
  // the total number of rows in the remote data set
  totalCount: 10,

  // you can map "values" and "totals" above to shorter names

  mappings: {
    values: "values",
    totals: "totals"
  }
}
```

**Example: Server-side pivoting example**

```ts
import {
  InfiniteTable,
  DataSource,
  GroupRowsState,
} from '@infinite-table/infinite-react';
import type {
  InfiniteTablePropColumns,
  InfiniteTablePropColumnPinning,
  DataSourceData,
  DataSourcePropAggregationReducers,
  DataSourceGroupBy,
  DataSourcePivotBy,
} from '@infinite-table/infinite-react';
import * as React from 'react';

type Developer = {
  id: number;
  firstName: string;
  lastName: string;
  country: string;
  city: string;
  currency: string;
  preferredLanguage: string;
  stack: string;
  canDesign: 'yes' | 'no';
  hobby: string;
  salary: number;
  age: number;
};

const DATA_SOURCE_SIZE = '10k';

const dataSource: DataSourceData<Developer> = ({
  pivotBy,
  aggregationReducers,
  groupBy,

  groupKeys = [],
}) => {
  const args = [
    pivotBy
      ? 'pivotBy=' + JSON.stringify(pivotBy.map((p) => ({ field: p.field })))
      : null,
    `groupKeys=${JSON.stringify(groupKeys)}`,
    groupBy
      ? 'groupBy=' + JSON.stringify(groupBy.map((p) => ({ field: p.field })))
      : null,
    aggregationReducers
      ? 'reducers=' +
        JSON.stringify(
          Object.keys(aggregationReducers).map((key) => ({
            field: aggregationReducers[key].field,
            id: key,
            name: aggregationReducers[key].reducer,
          })),
        )
      : null,
  ]
    .filter(Boolean)
    .join('&');
  return fetch(
    process.env.NEXT_PUBLIC_BASE_URL +
      `/developers${DATA_SOURCE_SIZE}-sql?` +
      args,
  )
    .then((r) => r.json())
    .then((data: Developer[]) => data);
};

const aggregationReducers: DataSourcePropAggregationReducers<Developer> = {
  salary: {
    // the aggregation name will be used as the column header
    name: 'Salary (avg)',
    field: 'salary',
    reducer: 'avg',
  },
  age: {
    name: 'Age (avg)',
    field: 'age',
    reducer: 'avg',
  },
};

const columns: InfiniteTablePropColumns<Developer> = {
  preferredLanguage: { field: 'preferredLanguage' },
  age: { field: 'age' },

  salary: {
    field: 'salary',
    type: 'number',
  },
  canDesign: { field: 'canDesign' },
  country: { field: 'country' },
  firstName: { field: 'firstName' },
  stack: { field: 'stack' },
  id: { field: 'id' },
  hobby: { field: 'hobby' },
  city: { field: 'city' },
  currency: { field: 'currency' },
};

// make the row labels column (id: 'labels') be pinned
const defaultColumnPinning: InfiniteTablePropColumnPinning = {
  // make the generated group columns pinned to start
  'group-by-country': 'start',
  'group-by-stack': 'start',
};

// make all rows collapsed by default
const groupRowsState = new GroupRowsState({
  expandedRows: [],
  collapsedRows: true,
});

export default function RemotePivotExample() {
  const groupBy: DataSourceGroupBy<Developer>[] = React.useMemo(
    () => [
      {
        field: 'country',
        column: {
          // give the group column for the country prop a custom id
          id: 'group-by-country',
        },
      },
      {
        field: 'stack',
        column: {
          // give the group column for the stack prop a custom id
          id: 'group-by-stack',
        },
      },
    ],
    [],
  );

  const pivotBy: DataSourcePivotBy<Developer>[] = React.useMemo(
    () => [
      { field: 'preferredLanguage' },
      {
        field: 'canDesign',
        // customize the column group
        columnGroup: ({ columnGroup }) => {
          return {
            ...columnGroup,
            header: `${
              columnGroup.pivotGroupKey === 'yes' ? 'Designer' : 'Non-designer'
            }`,
          };
        },
        // customize columns generated under this column group
        column: ({ column }) => ({
          ...column,
          header: `🎉 ${column.header}`,
        }),
      },
    ],
    [],
  );

  return (
    <DataSource<Developer>
      primaryKey="id"
      data={dataSource}
      groupBy={groupBy}
      pivotBy={pivotBy}
      aggregationReducers={aggregationReducers}
      defaultGroupRowsState={groupRowsState}
      lazyLoad={true}
    >
      {({ pivotColumns, pivotColumnGroups }) => {
        return (
          <InfiniteTable<Developer>
            debugId="remote-pivoting-example"
            defaultColumnPinning={defaultColumnPinning}
            columns={columns}
            hideEmptyGroupColumns
            pivotColumns={pivotColumns}
            pivotColumnGroups={pivotColumnGroups}
            columnDefaultWidth={220}
          />
        );
      }}
    </DataSource>
  );
}
```

The [`groupRenderStrategy`](https://infinite-table.com/docs/reference/infinite-table-props.md#groupRenderStrategy) prop is applicable even to pivoted tables, but `groupRenderStrategy="inline"` is not supported in this case.

### Another pivoting example with batching

Pivoting builds on the same data response as server-side grouping, but adds the pivot values for each group, as we already showed. Another difference is that in pivoting, no leaf rows are rendered or loaded, since this is pivoting and it only works with aggregated data. This means the `DataSource.data` function must always return the same format for the response data.

Just like server-side grouping, server-side pivoting also supports batching - make sure you specify [lazyLoad.batchSize](https://infinite-table.com/docs/reference/datasource-props/index.md#lazyLoad).

The example below also shows you how to customize the table rows while records are still loading.

**Example: Server side pivoting with lazy loding batching**

```ts
import {
  InfiniteTable,
  DataSource,
  GroupRowsState,
} from '@infinite-table/infinite-react';
import type {
  DataSourceData,
  InfiniteTablePropColumns,
  DataSourceGroupBy,
  DataSourcePropAggregationReducers,
  DataSourcePivotBy,
  InfiniteTableColumn,
  InfiniteTablePropColumnPinning,
  InfiniteTablePropGroupColumn,
} from '@infinite-table/infinite-react';
import * as React from 'react';

type Developer = {
  id: number;
  firstName: string;
  lastName: string;
  country: string;
  city: string;
  currency: string;
  preferredLanguage: string;
  stack: string;
  canDesign: 'yes' | 'no';
  hobby: string;
  salary: number;
  age: number;
};

const aggregationReducers: DataSourcePropAggregationReducers<Developer> = {
  salary: {
    name: 'Salary (avg)',
    field: 'salary',
    reducer: 'avg',
  },
  age: {
    name: 'Age (avg)',
    field: 'age',
    reducer: 'avg',
  },
};

const columns: InfiniteTablePropColumns<Developer> = {
  preferredLanguage: { field: 'preferredLanguage' },
  age: { field: 'age' },

  salary: {
    field: 'salary',
    type: 'number',
  },
  canDesign: { field: 'canDesign' },
  country: { field: 'country' },
  firstName: { field: 'firstName' },
  stack: { field: 'stack' },
  id: { field: 'id' },
  hobby: { field: 'hobby' },
  city: { field: 'city' },
  currency: { field: 'currency' },
};

const numberFormat = new Intl.NumberFormat(undefined, {
  style: 'currency',
  currency: 'USD',
});
const groupRowsState = new GroupRowsState({
  expandedRows: [],
  collapsedRows: true,
});

const groupColumn: InfiniteTablePropGroupColumn<Developer> = {
  id: 'group-col',
  // while loading, we can render a custom loading icon
  renderGroupIcon: ({ renderBag: { groupIcon }, data }) =>
    !data ? '🤷‍' : groupIcon,
  // while we have no data, we can render a placeholder
  renderValue: ({ data, value }) => (!data ? ' Loading...' : value),
};

const columnPinning: InfiniteTablePropColumnPinning = {
  'group-col': 'start',
};

const pivotColumnWithFormatter = ({
  column,
}: {
  column: InfiniteTableColumn<Developer>;
}) => {
  return {
    ...column,
    renderValue: ({ value }: { value: any }) =>
      value ? numberFormat.format(value as number) : 0,
  };
};
export default function RemotePivotExample() {
  const groupBy: DataSourceGroupBy<Developer>[] = React.useMemo(
    () => [
      {
        field: 'country',
      },
      { field: 'city' },
    ],
    [],
  );

  const pivotBy: DataSourcePivotBy<Developer>[] = React.useMemo(
    () => [
      {
        field: 'preferredLanguage',
        // for totals columns
        column: pivotColumnWithFormatter,
      },
      {
        field: 'canDesign',
        columnGroup: ({ columnGroup }) => {
          return {
            ...columnGroup,
            header:
              columnGroup.pivotGroupKey === 'yes'
                ? 'Designer 💅'
                : 'Non-Designer 💻',
          };
        },
        column: pivotColumnWithFormatter,
      },
    ],
    [],
  );

  const lazyLoad = React.useMemo(() => ({ batchSize: 10 }), []);
  return (
    <DataSource<Developer>
      primaryKey="id"
      data={dataSource}
      groupBy={groupBy}
      pivotBy={pivotBy.length ? pivotBy : undefined}
      aggregationReducers={aggregationReducers}
      defaultGroupRowsState={groupRowsState}
      lazyLoad={lazyLoad}
    >
      {({ pivotColumns, pivotColumnGroups }) => {
        return (
          <InfiniteTable<Developer>
            debugId="server-side-pivoting-with-lazy-load-batching-example"
            scrollStopDelay={10}
            columnPinning={columnPinning}
            columns={columns}
            groupColumn={groupColumn}
            groupRenderStrategy="single-column"
            columnDefaultWidth={220}
            pivotColumns={pivotColumns}
            pivotColumnGroups={pivotColumnGroups}
          />
        );
      }}
    </DataSource>
  );
}

const dataSource: DataSourceData<Developer> = ({
  pivotBy,
  aggregationReducers,
  groupBy,

  lazyLoadStartIndex,
  lazyLoadBatchSize,
  groupKeys = [],
  sortInfo,
}) => {
  if (sortInfo && !Array.isArray(sortInfo)) {
    sortInfo = [sortInfo];
  }
  const startLimit: string[] = [];
  if (lazyLoadBatchSize && lazyLoadBatchSize > 0) {
    const start = lazyLoadStartIndex || 0;
    startLimit.push(`start=${start}`);
    startLimit.push(`limit=${lazyLoadBatchSize}`);
  }
  const args = [
    ...startLimit,
    pivotBy
      ? 'pivotBy=' + JSON.stringify(pivotBy.map((p) => ({ field: p.field })))
      : null,
    `groupKeys=${JSON.stringify(groupKeys)}`,
    groupBy
      ? 'groupBy=' + JSON.stringify(groupBy.map((p) => ({ field: p.field })))
      : null,
    sortInfo
      ? 'sortInfo=' +
        JSON.stringify(
          sortInfo.map((s) => ({
            field: s.field,
            dir: s.dir,
          })),
        )
      : null,
    aggregationReducers
      ? 'reducers=' +
        JSON.stringify(
          Object.keys(aggregationReducers).map((key) => ({
            field: aggregationReducers[key].field,
            id: key,
            name: aggregationReducers[key].reducer,
          })),
        )
      : null,
  ]
    .filter(Boolean)
    .join('&');
  return fetch(
    process.env.NEXT_PUBLIC_BASE_URL + `/developers30k-sql?` + args,
  ).then((r) => r.json());
};
```

Here's another example, that assumes grouping by `country` and `city`, aggregations by `age` and `salary` (average values) and pivot by `preferredLanguage` and `canDesign` (a boolean property):

```tsx
//request:
groupKeys: [] // empty keys array, so it's a top-level group
groupBy: [{"field":"country"},{"field":"city"}]
reducers: [{"field":"salary","id":"avgSalary","name":"avg"},{"field":"age","id":"avgAge","name":"avg"}]
lazyLoadStartIndex: 0
lazyLoadBatchSize: 10
pivotBy: [{"field":"preferredLanguage"},{"field":"canDesign"}]

//response
{
  cache: true,
  totalCount: 20,
  data: [
    {
      data: {country: "Argentina"},
      aggregations: {avgSalary: 20000, avgAge: 30},
      keys: ["Argentina"],
      pivot: {
        totals: {avgSalary: 20000, avgAge: 30},
        values: {
          Csharp: {
            totals: {avgSalary: 19000, avgAge: 29},
            values: {
              no: {totals: {salary: 188897, age: 34}},
              yes: {totals: {salary: 196000, age: 36}}
            }
          },
          Go: {
            totals: {salary: 164509, age: 36},
            values: {
              no: {totals: {salary: 189202, age: 37}},
              yes: {totals: {salary: 143977, age: 35}}
            }
          },
          Java: {
            totals: {salary: 124809, age: 32},
            values: {
              no: {totals: {salary: 129202, age: 47}},
              yes: {totals: {salary: 233977, age: 25}}
            }
          },
          //...
        }
      }
    },
    //...
  ]
}
```

If we were to scroll down, the next batch of data would have the same structure as the previous one, but with `lazyLoadStartIndex` set to 10 (if `lazyLoad.batchSize = 10`).

Now let's expand the first group and see how the request/response would look like:

```tsx
//request:
groupKeys: ["Argentina"]
groupBy: [{"field":"country"},{"field":"city"}]
reducers: [{"field":"salary","id":"avgSalary","name":"avg"},{"field":"age","id":"avgAge","name":"avg"}]
lazyLoadStartIndex: 0
lazyLoadBatchSize: 10
pivotBy: [{"field":"preferredLanguage"},{"field":"canDesign"}]

//response
{
  mappings: {
    totals: "totals",
    values: "values"
  },
  cache: true,
  totalCount: 20,
  data: [
    {
      data: {country: "Argentina", city: "Buenos Aires"},
      aggregations: {avgSalary: 20000, avgAge: 30},
      keys: ["Argentina", "Buenos Aires"],
      pivot: {
        totals: {avgSalary: 20000, avgAge: 30},
        values: {
          Csharp: {
            totals: {avgSalary: 39000, avgAge: 29},
            values: {
              no: {totals: {salary: 208897, age: 34}},
              yes: {totals: {salary: 296000, age: 36}}
            }
          },
          Go: {
            totals: {salary: 164509, age: 36},
            values: {
              no: {totals: {salary: 189202, age: 37}},
              yes: {totals: {salary: 143977, age: 35}}
            }
          },
          Java: {
            totals: {salary: 124809, age: 32},
            values: {
              no: {totals: {salary: 129202, age: 47}},
              yes: {totals: {salary: 233977, age: 25}}
            }
          },
          //...
        }
      }
    },
    //...
  ]
}
```

The response can contain a `mappings` key with values for `totals` and `values` keys - this can be useful for making the server-side pivot response lighter.

If `mappings` would be `{totals: "t", values: "v"}`, the response would look like this:

```tsx
{
  totalCount: 20,
  data: {...},
  pivot: {
    t: {avgSalary: 10000, avgAge: 30},
    v: {
      Go: {
        t: {...},
        v: {...}
      },
      Java: {
        t: {...},
        v: {...}
      }
    }
  }

```

More-over, you can also give aggregationReducers shorter keys to make the server response even more compact

```tsx
const aggregationReducers: DataSourcePropAggregationReducers<Developer> =
  {
    s: {
      name: 'Salary (avg)',
      field: 'salary',
      reducer: 'avg',
    },
    a: {
      name: 'Age (avg)',
      field: 'age',
      reducer: 'avg',
    },
  };

// pivot response
{
  totalCount: 20,
  data: {...},
  pivot: {
    t: {s: 10000, a: 30},
    v: {
      Go: {
        t: { s: 10000, a: 30 },
        v: {...}
      },
      Java: {
        t: {...},
        v: {...}
      }
    }
  }
```

Adding a `cache: true` key to the resolved object in the `DataSource.data` call will cache the value for the expanded group, so that when collaped and expanded again, the cached value will be used, and no new call is made to the `DataSource.data` function. This is applicable for both pivoted and/or grouped data. Not passing `cache: true` will make the function call each time the group is expanded.