Pivoting

Column Pivoting

Column pivoting transforms values of a column into new columns in your grid. Set the columnPivotModel property on grid state to enable this feature.

LyteNyte Grid creates unique state for pivot columns with their own sort and filter settings.

Your underlying data source must support column pivoting. Since pivots derive from row values, creating columns from these values depends on your data source. Both the Client Row Data Source and Server Row Data Source support column pivoting.

Pivoting

To start column pivoting:

Set the value of the columnPivotModel to the pivot configuration to display.
Set columnPivotMode to true on the grid state.

The example below shows the simplest column pivot configuration the grid may use. Here, we pivot on the job column, turning its values into columns and measure the balance column as a sum of our pivoted job columns.

Pivoting

"use client";
 
import {
  Grid,
  GROUP_COLUMN_SINGLE_ID,
  useClientRowDataSource,
} from "@1771technologies/lytenyte-pro";
import "@1771technologies/lytenyte-pro/grid.css";
import {
  ChevronDownIcon,
  ChevronRightIcon,
} from "@1771technologies/lytenyte-pro/icons";
import type { Column } from "@1771technologies/lytenyte-pro/types";
import { bankDataSmall } from "@1771technologies/sample-data/bank-data-smaller";
import { useId } from "react";
 
type BankData = (typeof bankDataSmall)[number];
 
const columns: Column<BankData>[] = [
  { id: "age", type: "number" },
  { id: "job" },
  { id: "balance", type: "number" },
  { id: "education" },
  { id: "marital" },
];
 
export function ColumnPivoting() {
  const ds = useClientRowDataSource({
    data: bankDataSmall,
  });
 
  const grid = Grid.useLyteNyte({
    gridId: useId(),
    rowDataSource: ds,
    columns,
 
    rowGroupColumn: {
      cellRenderer: ({ grid, row, column }) => {
        if (!grid.api.rowIsGroup(row)) return null;
 
        const field = grid.api.columnField(column, row);
        const isExpanded = grid.api.rowGroupIsExpanded(row);
 
        const canBeExpanded =
          row.depth + 1 !== grid.state.columnPivotModel.get().rows.length;
 
        return (
          <div
            className="flex items-center gap-2 w-full h-full"
            style={{ paddingLeft: row.depth * 16 }}
          >
            {canBeExpanded && (
              <button
                className="flex items-center justify-center"
                onClick={(e) => {
                  e.stopPropagation();
                  grid.api.rowGroupToggle(row);
                }}
              >
                {!isExpanded && <ChevronRightIcon />}
                {isExpanded && <ChevronDownIcon />}
              </button>
            )}
 
            <div>{`${field}`}</div>
          </div>
        );
      },
    },
 
    columnPivotMode: true,
    columnPivotModel: {
      columns: [{ field: "job" }],
      rows: [],
      sorts: [],
      values: [{ field: "balance", aggFn: "sum" }],
      filters: {},
      filtersIn: {},
    },
  });
 
  const view = grid.view.useValue();
 
  return (
    <div className="lng-grid" style={{ height: 160 }}>
      <Grid.Root grid={grid}>
        <Grid.Viewport>
          <Grid.Header>
            {view.header.layout.map((row, i) => {
              return (
                <Grid.HeaderRow key={i} headerRowIndex={i}>
                  {row.map((c) => {
                    if (c.kind === "group") {
                      return (
                        <Grid.HeaderGroupCell
                          key={c.idOccurrence}
                          cell={c}
                          className="flex items-center gap-2 px-2"
                        >
                          <div>{c.groupPath.at(-1)}</div>
                        </Grid.HeaderGroupCell>
                      );
                    }
 
                    return (
                      <Grid.HeaderCell
                        key={c.id}
                        cell={c}
                        className="flex w-full h-full capitalize px-2 items-center"
                      >
                        {c.id === GROUP_COLUMN_SINGLE_ID
                          ? "Group"
                          : c.id.split("-->").at(-1)}
                      </Grid.HeaderCell>
                    );
                  })}
                </Grid.HeaderRow>
              );
            })}
          </Grid.Header>
          <Grid.RowsContainer>
            <Grid.RowsCenter>
              {view.rows.center.map((row) => {
                if (row.kind === "full-width") return null;
 
                return (
                  <Grid.Row row={row} key={row.id}>
                    {row.cells.map((c) => {
                      return (
                        <Grid.Cell
                          key={c.id}
                          cell={c}
                          className="text-sm flex items-center h-full w-full px-2"
                        />
                      );
                    })}
                  </Grid.Row>
                );
              })}
            </Grid.RowsCenter>
          </Grid.RowsContainer>
        </Grid.Viewport>
      </Grid.Root>
    </div>
  );
}

Pivots and Row Groups

Pivots are an aggregate operation. They only make sense when data is aggregated across the pivot column. LyteNyte Grid supports break these aggregations into groups by supplying the columnPivotModel with rows to group on. The example below does this by grouping on the education and marital columns.

Pivots With Row Grouping

"use client";
 
import {
  Grid,
  GROUP_COLUMN_SINGLE_ID,
  useClientRowDataSource,
} from "@1771technologies/lytenyte-pro";
import "@1771technologies/lytenyte-pro/grid.css";
import {
  ChevronDownIcon,
  ChevronRightIcon,
} from "@1771technologies/lytenyte-pro/icons";
import type { Column } from "@1771technologies/lytenyte-pro/types";
import { bankDataSmall } from "@1771technologies/sample-data/bank-data-smaller";
import { useId } from "react";
 
type BankData = (typeof bankDataSmall)[number];
 
const columns: Column<BankData>[] = [
  { id: "age", type: "number" },
  { id: "job" },
  { id: "balance", type: "number" },
  { id: "education" },
  { id: "marital" },
];
 
export function ColumnPivotingRowGroups() {
  const ds = useClientRowDataSource({
    data: bankDataSmall,
  });
 
  const grid = Grid.useLyteNyte({
    gridId: useId(),
    rowDataSource: ds,
    columns,
 
    rowGroupColumn: {
      cellRenderer: ({ grid, row, column }) => {
        if (!grid.api.rowIsGroup(row)) return null;
 
        const field = grid.api.columnField(column, row);
        const isExpanded = grid.api.rowGroupIsExpanded(row);
 
        const canBeExpanded =
          row.depth + 1 < grid.state.columnPivotModel.get().rows.length;
 
        return (
          <div
            className="flex items-center gap-2 w-full h-full"
            style={{ paddingLeft: row.depth * 16 + (!canBeExpanded ? 20 : 0) }}
          >
            {canBeExpanded && (
              <button
                className="flex items-center justify-center"
                onClick={(e) => {
                  e.stopPropagation();
                  grid.api.rowGroupToggle(row);
                }}
              >
                {!isExpanded && <ChevronRightIcon />}
                {isExpanded && <ChevronDownIcon />}
              </button>
            )}
 
            <div>{`${field}`}</div>
          </div>
        );
      },
    },
 
    columnPivotMode: true,
    columnPivotModel: {
      columns: [{ field: "job" }],
      rows: [{ field: "education" }, { field: "marital" }],
      sorts: [],
      values: [{ field: "balance", aggFn: "sum" }],
      filters: {},
      filtersIn: {},
    },
  });
 
  const view = grid.view.useValue();
 
  return (
    <div className="lng-grid" style={{ height: 500 }}>
      <Grid.Root grid={grid}>
        <Grid.Viewport>
          <Grid.Header>
            {view.header.layout.map((row, i) => {
              return (
                <Grid.HeaderRow key={i} headerRowIndex={i}>
                  {row.map((c) => {
                    if (c.kind === "group") {
                      return (
                        <Grid.HeaderGroupCell
                          key={c.idOccurrence}
                          cell={c}
                          className="flex items-center gap-2 px-2"
                        >
                          <div>{c.groupPath.at(-1)}</div>
                        </Grid.HeaderGroupCell>
                      );
                    }
 
                    return (
                      <Grid.HeaderCell
                        key={c.id}
                        cell={c}
                        className="flex w-full h-full capitalize px-2 items-center"
                      >
                        {c.id === GROUP_COLUMN_SINGLE_ID
                          ? "Group"
                          : c.id.split("-->").at(-1)}
                      </Grid.HeaderCell>
                    );
                  })}
                </Grid.HeaderRow>
              );
            })}
          </Grid.Header>
          <Grid.RowsContainer>
            <Grid.RowsCenter>
              {view.rows.center.map((row) => {
                if (row.kind === "full-width") return null;
 
                return (
                  <Grid.Row row={row} key={row.id}>
                    {row.cells.map((c) => {
                      return (
                        <Grid.Cell
                          key={c.id}
                          cell={c}
                          className="text-sm flex items-center h-full w-full px-2"
                        />
                      );
                    })}
                  </Grid.Row>
                );
              })}
            </Grid.RowsCenter>
          </Grid.RowsContainer>
        </Grid.Viewport>
      </Grid.Root>
    </div>
  );
}

PreviousAggregations

Next Column Filters