catalog.fn.pivot()

Create multi-dimensional pivot tables for cross-tabulation analysis. Organize data into rows and columns with aggregated values at each intersection.

Syntax

list.catalog.fn.pivot(options)

Parameters

rowsArray<{ attribute: string; calculateTotals?: boolean }>Required: Row dimensions; several entries nest (first = outermost); [] for none. calculateTotals: false leaves the row totals empty (first row dimension only)
columnsArray<{ attribute: string; calculateTotals?: boolean }>Required: Column dimensions (pass [] for none; without columns the result is { key: "invalid_options" }). calculateTotals: false leaves the column totals empty
valuesAggregationOptions[]Required: Aggregations ({ attribute, types, ... } as in aggregate()) computed for every row, column and cell. 'distinct' counts and keys only the items of that row, column or cell. A dotted value attribute (customer.rating) resolves through the reference
options.calculateTotalsbooleanOptional: Compute the grand totals in pivot.values (default: true)

Return type

Returns a nested PivotResponse. Each dimension adds a level: pivot.rows[0] is the first row dimension, its rows are the row values, and in each row columns[0].columns are the cells:

// PivotResponse can be imported from @dcupl/common. PivotResponseItem is not exported:
// type PivotResponseItem = NonNullable<PivotResponse['rows']>[number];
type PivotResponse = {
  key: string;                    // 'root'
  values?: Aggregation[];         // Grand totals ([] with options.calculateTotals: false)
  rows?: PivotResponseItem[];     // [0] = first row dimension
  columns?: Omit<PivotResponseItem, 'rows'>[]; // [0] = first column dimension (column totals)
}

type PivotResponseItem = {
  key: string;                    // Dimension level: the attribute; value level: the raw value
                                  // (a number for a numeric attribute, e.g. 2024)
  value?: any;                    // The row/column value (value level only)
  values?: Aggregation[];         // Aggregations for this row, column or cell
  rows?: PivotResponseItem[];     // Dimension level: one entry per value;
                                  // value level: the next row dimension
  columns?: Omit<PivotResponseItem, 'rows'>[]; // Dimension level: one entry per value;
                                  // row value: the first column dimension with its cells;
                                  // column value: the next column dimension (never rows)
}

// Example: rows [region], columns [product]
// pivot.rows[0]                         -> { key: 'region', rows: [...] }
// pivot.rows[0].rows[0]                 -> { key: 'North', value: 'North', values, columns }
// pivot.rows[0].rows[0].columns[0].columns[0] -> cell { key, value, values }

Usage notes

Pivot tables respect current query filters - results update as filters change
Supports multiple row and column dimensions for hierarchical grouping
With a second column dimension, each cell of the first one nests the cells of the second in its columns[0].columns, forming one column tree A > B; column cells never carry a rows entry
Items without a value for a row or column dimension are collected in extra entries: one whose key and value are undefined and, for null values, one whose key and value are null. Like any other entry, each carries values plus its cells and nested dimensions. Skip both (key != null) when you render the table. Items whose array value is empty ([]) appear in no entry of that dimension and count only in the totals
Dotted paths work for row and column dimensions (e.g., "product.category" or "customer.region")
Set options.calculateTotals: false to skip the grand totals, or calculateTotals: false on the first row dimension or the first column dimension to skip its totals (cells are always computed)
Ideal for sales reports, time-based analysis, and cross-tabulations
Performance: Limit row/column dimensions - each dimension multiplies result size
Performance: Pre-filter data before pivoting to reduce the dataset
Performance: Request only needed aggregation types in values array
Combinations without items are left out: a row only lists the column values that occur in it, so look cells up by value instead of by index

Related methods

catalog.fn.groupBy() — Simpler single-dimension grouping
catalog.fn.aggregate() — Compute aggregations without grouping
catalog.fn.facets() — Get unique values before pivoting

Related guides/docs

Functions API reference: global dcupl.fn.* form and errors

Examples

Sales by region and product

Classic pivot table with regions as rows, products as columns, and revenue/count as values.

const orderList = dcupl.lists.create({ modelKey: 'Order' });

const pivot = orderList.catalog.fn.pivot({
  rows: [{ attribute: 'region' }],
  columns: [{ attribute: 'product' }],
  values: [
    { attribute: 'totalAmount', types: ['sum'] },
    { attribute: 'key', types: ['count'] },
  ],
});

// pivot.rows[0] is the 'region' dimension; its rows are the regions.
// In each region, columns[0] is the 'product' dimension; its columns are the cells.
// Entries without a key (undefined or null) collect the orders without a region or product: skip them.
const regions = (pivot.rows?.[0]?.rows ?? []).filter((row) => row.key != null);

regions.forEach((row) => {
  console.log('Region: ' + row.value);

  const cells = (row.columns?.[0]?.columns ?? []).filter((col) => col.key != null);
  cells.forEach((col) => {
    const revenue = col.values?.[0]?.sum ?? 0;
    const orders = col.values?.[1]?.count ?? 0;
    console.log('  ' + col.value + ': $' + revenue + ' (' + orders + ' orders)');
  });
});

// Grand totals
console.log('Grand Total:', pivot.values?.[0]?.sum);

Navigating pivot results

Each dimension adds a level to the result. A row only has cells for the column values that occur in it, so look cells up by value, not by index.

const pivot = list.catalog.fn.pivot({
  rows: [{ attribute: 'category' }],
  columns: [{ attribute: 'year' }],
  values: [{ attribute: 'revenue', types: ['sum'] }],
});

// Grand total over all data
const grandTotal = pivot.values?.[0]?.sum;
console.log('Grand Total:', grandTotal);

// Column headers: pivot.columns[0] is the 'year' dimension (its entries carry the column totals).
// Entries without a key (undefined or null) collect the items without a value: skip them.
const years = (pivot.columns?.[0]?.columns ?? [])
  .filter((col) => col.key != null)
  .map((col) => col.value);
console.log('Columns:', years);

// Rows: pivot.rows[0] is the 'category' dimension
const categories = (pivot.rows?.[0]?.rows ?? []).filter((row) => row.key != null);

// Extract data for chart visualization
const chartData = categories.map((row) => {
  const cells = row.columns?.[0]?.columns ?? [];
  return {
    category: row.value,
    rowTotal: row.values?.[0]?.sum ?? 0,
    byYear: Object.fromEntries(
      years.map((year) => [year, cells.find((cell) => cell.value === year)?.values?.[0]?.sum ?? 0])
    ),
  };
});
console.log(chartData);

// Access a specific cell: first category, second year
const cell = categories[0]?.columns?.[0]?.columns?.find((c) => c.value === years[1]);
console.log('First category, second year:', cell?.values?.[0]?.sum ?? 0);

Two column dimensions

A second column dimension nests in the first: each channel cell holds its status cells, so the columns form one tree, channel > status. Column cells never hold rows.

const orderList = dcupl.lists.create({ modelKey: 'Order' });

const pivot = orderList.catalog.fn.pivot({
  rows: [{ attribute: 'region' }],
  columns: [{ attribute: 'channel' }, { attribute: 'status' }],
  values: [{ attribute: 'totalAmount', types: ['sum'] }],
});

// The cells of an item's next column dimension, without the entries for missing values
const cellsOf = (item) => (item?.columns?.[0]?.columns ?? []).filter((col) => col.key != null);

const regions = (pivot.rows?.[0]?.rows ?? []).filter((row) => row.key != null);

regions.forEach((row) => {
  console.log(row.value + ': ' + row.values?.[0]?.sum);
  cellsOf(row).forEach((channel) => {
    console.log('  ' + channel.value + ': ' + channel.values?.[0]?.sum);
    cellsOf(channel).forEach((status) => {
      console.log('    ' + status.value + ': ' + status.values?.[0]?.sum);
    });
  });
});

// pivot.columns holds the same channel > status tree over all regions: the column totals
const web = cellsOf(pivot).find((col) => col.value === 'web');
const webOpen = cellsOf(web).find((col) => col.value === 'open');
console.log('All regions, web > open: ' + webOpen?.values?.[0]?.sum);

// For the orders North/web/open 100, North/web/closed 200, North/store/closed 50
// and South/web/open 300, this prints:
// North: 350
//   web: 300
//     open: 100
//     closed: 200
//   store: 50
//     closed: 50
// South: 300
//   web: 300
//     open: 300
// All regions, web > open: 400

Deep property pivot

Pivot using referenced model properties via dot notation for cross-model analysis.

const orderList = dcupl.lists.create({ modelKey: 'Order' });

// Pivot by customer country and product brand (deep properties)
const pivot = orderList.catalog.fn.pivot({
  rows: [{ attribute: 'customer.country' }],
  columns: [{ attribute: 'product.brand.name' }],
  values: [
    { attribute: 'totalAmount', types: ['sum'] },
    { attribute: 'key', types: ['count'] },
  ],
});

console.log('Sales by Country and Brand:');
(pivot.rows?.[0]?.rows ?? []).forEach((row) => {
  console.log('\n' + row.value + ':');

  (row.columns?.[0]?.columns ?? []).forEach((col) => {
    const revenue = col.values?.[0]?.sum ?? 0;
    const orders = col.values?.[1]?.count ?? 0;

    if (revenue > 0) {
      console.log('  ' + col.value + ': $' + revenue + ' (' + orders + ' orders)');
    }
  });
});

// Basic pivot: category rows × brand columns
const pivot = list.catalog.fn.pivot({
  rows: [{ attribute: 'category' }],
  columns: [{ attribute: 'brand' }],
  values: [{ attribute: 'key', types: ['count'] }]
});

// Multi-dimensional pivot with multiple aggregations
const salesPivot = list.catalog.fn.pivot({
  rows: [{ attribute: 'sport' }, { attribute: 'category' }],
  columns: [{ attribute: 'brand' }],
  values: [
    { attribute: 'price', types: ['sum', 'avg'] },
    { attribute: 'inStock', types: ['sum'] }
  ]
});

{}