Pivoting

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 prop. It's an array of objects, each with a field property bound (so pivotBy[].field is keyof DATA_TYPE) to the DataSource.

Note

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

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}
  />
} }
<
Pivoting with avg aggregation
View Mode
Fork
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('https://infinite-table.com/.netlify/functions/json-server' + '/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>
              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. 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.

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,
    },
  },
};
Pivot columns inherit from original columns bound to the same field
View Mode
Fork
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('https://infinite-table.com/.netlify/functions/json-server' + '/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>
              columns={columns}
              hideEmptyGroupColumns
              pivotColumns={pivotColumns}
              pivotColumnGroups={pivotColumnGroups}
              columnDefaultWidth={180}
            />
          );
        }}
      </DataSource>
    </>
  );
}

Another way to do it is to specify 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.

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.

const pivotBy: DataSourcePivotBy<DATA_TYPE>[] = [
  { field: 'country' },
  {
    field: 'canDesign',
    column: ({ column }) => {
      return {
        header: column.pivotGroupKey === 'yes' ? 'Designer' : 'Not a Designer',
      };
    },
  },
];
Pivoting with customized pivot column
View Mode
Fork
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('https://infinite-table.com/.netlify/functions/json-server' + '/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>
              columns={columns}
              hideEmptyGroupColumns
              pivotColumns={pivotColumns}
              pivotColumnGroups={pivotColumnGroups}
              columnDefaultWidth={180}
            />
          );
        }}
      </DataSource>
    </>
  );
}

Total and grand-total columns

In pivot mode you can configure both total columns and grand-total columns. By default, grand-total columns are not displayed, so you have to explicitly set the pivotGrandTotalColumnPosition prop for them to be visible.

Pivoting with customized position for totals and grand-total columns
View Mode
Fork
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('https://infinite-table.com/.netlify/functions/json-server' + '/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',
  },
];

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>
              groupRenderStrategy="single-column"
              columns={columns}
              columnDefaultWidth={200}
              pivotColumns={pivotColumns}
              pivotColumnGroups={pivotColumnGroups}
              pivotTotalColumnPosition="end"
              pivotGrandTotalColumnPosition="start"
            />
          );
        }}
      </DataSource>
    </>
  );
}

Note

What are grand-total columns?

For each aggregation reducer specified in the DataSource, you can have a total column - this is what grand-total columns basically are.

Server-side pivoting

By default, pivoting is client side. However, if you specify DataSource.lazyLoad and provide a function that returns a promise for the DataSource.data prop, the table will use server-pivoted data.

The DataSource.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}.

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' },
};

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}


{
  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
Server-side pivoting example
View Mode
Fork
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(
    'https://infinite-table.com/.netlify/functions/json-server' +
      `/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>
            defaultColumnPinning={defaultColumnPinning}
            columns={columns}
            hideEmptyGroupColumns
            pivotColumns={pivotColumns}
            pivotColumnGroups={pivotColumnGroups}
            columnDefaultWidth={220}
          />
        );
      }}
    <

Note

The 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.

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

Server side pivoting with lazy loding batching
View Mode
Fork
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>
            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

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):

//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:

//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}}
            }
          },
          //...
        }
      }
    },
    //...
  ]
}

Note

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:

{
  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

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: {...}
      }
    }
  }

Note

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.