React Data GridPivoting

react logo

Pivoting allows you to take a columns values and turn them into columns. For example you can pivot on Country to make columns for Ireland, United Kingdom, USA etc.

Pivoting only makes sense when mixed with aggregation. If you turn a column into a pivot column, you must have at least one aggregation (value) active for the configuration to make sense. For example, if pivoting by country, you must provide something you are measuring such as 'gold medals per country'.

Pivot Mode

Pivot mode is required to be turned on for pivoting to work. When the grid is in pivot mode, the following will happen:

  • Only columns with Group, Pivot or Value active will be included in the grid.
  • Only aggregated rows will be shown, the lowest level rowData will not be displayed.

If pivot mode is off, then adding or removing pivot columns will have no effect.

To allow a column to be used as pivot column via the Tool Panel, set enablePivot=true on the required columns. Otherwise you won't be able to drag and drop the columns to the pivot drop zone from the Tool Panel.

Specifying Pivot Columns

To pivot rows by a particular column, mark the column you want to group with pivot=true. There is no limit on the number of columns that the grid can pivot by. For example, the following will pivot the rows in the grid by country and then sport:

const [columnDefs, setColumnDefs] = useState([
    { field: "country", pivot: true },
    { field: "sport", pivot: true }

<AgGridReact columnDefs={columnDefs} />

Example: Simple Pivot

The example below shows a simple pivot on the Sport column using the Gold, Silver and Bronze columns for values.

Columns Date and Year, although defined as columns, are not displayed in the grid as they have no group, pivot or value associated with them.

Pivot Mode vs Pivot Active

It is possible to have pivot mode turned on even though there is no pivot active on the grid. In this scenario, the grid will display the data as normal but will strip out columns that have no grouping or value active.

The example below demonstrates the difference between pivot mode and having a column with pivot active. The example has three modes of operation that can be switched between using the top buttons. The modes are as follows:

  • 1 - Grouping Active: This is normal grouping. The grid groups with aggregations over Gold, Silver and Bronze. The user can drill down to the lowest level row data and columns without aggregation or group (eg Country, Year, Date and Sport) are shown.

  • 2 - Grouping Active with Pivot Mode: This is grouping with pivotMode=true, but without any pivot active. The data shown is identical to the first option except the grid removes access to the lowest level row data and columns without aggregation or group are not shown.

  • 3 - Grouping Active with Pivot Mode and Pivot Active: This is grouping with pivotMode=true and pivot active. Although it appears similar to the second option, there is no pivot active in the second option.

Note that a pivot can only be active if pivot mode is on. If pivot mode is off, all pivot columns are ignored.

Pivot Mode & Visible Columns

When not in pivot mode, only columns that are visible are shown in the grid. To remove a column from the grid, use api.setColumnsVisible([colKey], visible). Checking a column in the toolPanel will set the visibility on the column.

When in pivot mode and not pivoting, only columns that have row group or aggregation active are included in the grid. To add a column to the grid you either add it as a row group column or a value column. Setting visibility on a column has no impact when in pivot mode. Checking a column in the toolPanel will either add the column as a row group (if the column is configured as a dimension) or as an aggregated value (if the columns is configured as a value).

When in pivot mode and pivoting, then the columns displayed in the grid are pivot result columns (explained below) and not the grid options supplied columns. The pivot result columns are a product of the pivot and value columns. To have a column included in the calculation of the pivot result columns, it should be added as either a pivot or a value column. As with 'pivot mode and not pivoting', checking a column in the toolPanel while in pivot mode will add the column as a row group or an aggregated value. You must drag the column to a pivot drop zone in order to add it as a pivot column. As before, setting visibility on the column will have no effect when in pivot mode.

Columns vs Pivot Result Columns

When Pivot Mode is off, the Columns in the grid correspond to the Column Definitions provided to the grid by the application. When Pivot Mode is on and Pivot is active, the Columns in the grid are Pivot Result Columns, which are the combinatorial product of Pivot Column values and the aggregated Value Columns.

For example, consider the Columns from the examples Year and Gold. If a Pivot is placed on Year and an Aggregation of sum is placed on Gold, then the Pivot Result Columns that get displayed in the grid would be: 2002 sum(Gold), 2004 sum(Gold), 2006 sum(Gold), 2008 sum(Gold), 2010 sum(Gold) and 2012 sum(Gold).

The Pivot Result Columns do not behave the same as the initial Columns in the following scenarios:

  • Tool Panel: The Tool Panel shows initial Columns, never Pivot Result Columns.
  • Filtering: Filtering on Pivot Result Columns is slightly different, See below for details.

Looking up Pivot Result Columns

As mentioned above, the Pivot Result Columns in the grid are created by the grid by cross referencing Pivot Columns with Value Columns. The result of which are new Pivot Result Columns that have Column ID's generated by the grid. If you want to use the Column API to manage the Pivot Result Columns (e.g. to set width, apply sort etc) you look up the column using the Columns API getPivotResultColumn(pivotCols, valueCol).