or run

npx @tessl/cli init

Version

Tile

Overview

Evals

Files

docs

guides

authorization.md building-queries.md compilation.md visualization.md working-with-data.md

quick-reference

common-apis.md imports.md types-at-a-glance.md

reference

architecture.md getting-started-full.md

index.md

tile.json

Pivot Tables

Pivot table configuration and query result transformation for creating cross-tabulated data views.

Overview

The pivot module provides functions for:

Deriving pivot configurations from chart configurations
Transforming query results into pivot format
Managing pivot values columns
Configuring pivot table layouts

Pivot tables allow you to transform rows of data into a cross-tabulated format where:

One dimension becomes row headers
Another dimension becomes column headers
Metrics are displayed at intersections

Pivot Configuration Types

PivotConfig

interface PivotConfig {
  /** Column field IDs (required) - defines which fields appear as columns */
  columns: string[];
  /** Dimension field IDs to pivot into columns */
  pivotDimensions?: string[];
  /** Display metrics as rows instead of columns */
  metricsAsRows?: boolean;
  /** Custom column ordering */
  columnOrder?: string[];
  /** Metric field IDs to hide from display */
  hiddenMetricFieldIds?: string[];
  /** Show column total calculations */
  columnTotals?: boolean;
  /** Show row total calculations */
  rowTotals?: boolean;
}

Example:

const pivotConfig: PivotConfig = {
  columns: ['orders.region', 'orders.total_revenue', 'orders.order_count'],
  pivotDimensions: ['orders.region'],       // Regions as columns
  metricsAsRows: false,                     // Metrics as columns
  columnTotals: true,                       // Show column totals
  rowTotals: true,                          // Show row totals
};

// Results in a table like:
//                    | North | South | East | West | Total |
// Total Revenue      | 1000  | 1500  | 2000 | 1200 | 5700  |
// Order Count        | 50    | 60    | 80   | 55   | 245   |
// Total              | 1050  | 1560  | 2080 | 1255 | 5945  |

PivotConfiguration

/**
 * Configuration for executing pivoted queries
 * Used in AsyncQueryService to generate SQL for pivoted queries
 */
interface PivotConfiguration {
  /** Column(s) to use as row index */
  indexColumn: PivotIndexColum | PivotIndexColum[] | undefined;
  /** Metric columns to aggregate */
  valuesColumns: ValuesColumn[];
  /** Dimension columns to pivot */
  groupByColumns: GroupByColumn[] | undefined;
  /** Sort configuration for pivot results */
  sortBy: SortBy | undefined;
}

PivotData

/**
 * Structured pivot table data output
 */
interface PivotData {
  /** Field type information for header rows */
  headerValueTypes: Field[];
  /** Header values organized as rows of field values */
  headerValues: FieldValue[][];
  /** Field type information for index columns */
  indexValueTypes: Field[];
  /** Index values organized as rows of field values */
  indexValues: FieldValue[][];
  /** Number of data columns in the pivot */
  dataColumnCount: number;
  /** Data values organized as matrix */
  dataValues: (ResultValue | null)[][];
  /** Title field configuration for display */
  titleFields: (TitleField | null)[][];
  /** Row total field configuration */
  rowTotalFields?: (TotalField | null)[][];
  /** Column total field configuration */
  columnTotalFields?: (TotalField | null)[][];
  /** Calculated row totals */
  rowTotals?: (number | null)[][];
  /** Calculated column totals */
  columnTotals?: (number | null)[][];
  /** Total number of cells in pivot */
  cellsCount: number;
  /** Total number of rows in pivot */
  rowsCount: number;
  /** Original pivot configuration */
  pivotConfig: PivotConfig;
  /** Retrofit data for compatibility */
  retrofitData: {
    allCombinedData: ResultRow[];
    pivotColumnInfo: PivotColumn[];
  };
  /** Grouped subtotals if calculated */
  groupedSubtotals?: Record<string, Record<string, number>[]>;
}

PivotColumn

/**
 * Pivot column metadata
 */
interface PivotColumn {
  /** Display field ID */
  fieldId: string;
  /** Base dimension field ID */
  baseId: string | undefined;
  /** Underlying metric or dimension ID */
  underlyingId: string | undefined;
  /** Column type classification */
  columnType: string | undefined;
}

PivotReference

interface PivotReference {
  field: string;
  pivotValues?: PivotValue[];
}

interface PivotValue {
  field: string;
  value: unknown;
}

PivotValuesColumn

interface PivotValuesColumn {
  reference: string;
  label: string;
  pivotReference?: PivotReference;
}

Represents a generated column in a pivoted result set.

PivotIndexColum

/**
 * Index column configuration for pivot tables
 * Note: The name PivotIndexColum (without 'n') matches the source code naming
 */
type PivotIndexColum = {
  reference: string;
  type: VizIndexType;
};

Defines the index column(s) used for row headers in pivot tables, with field reference and axis type.

ValuesColumn

/**
 * Values column for SQL Runner pivot queries
 * Represents a metric to be aggregated in the pivot table
 */
interface ValuesColumn {
  /** Reference to the metric field */
  reference: string;
  /** Aggregation function to apply */
  aggregation: VizAggregationOptions;
}

Represents a metric column that will be aggregated in pivot queries.

GroupByColumn

/**
 * Group by column for SQL Runner pivot queries
 * Represents a dimension to pivot into columns
 */
interface GroupByColumn {
  /** Reference to the dimension field */
  reference: string;
}

Represents a dimension field that will generate pivot columns (cross-tabulation).

Pivot Functions

Core Pivot Functions

function pivotQueryResults(args: {
  pivotConfig: PivotConfig;
  metricQuery: Pick<
    MetricQuery,
    'dimensions' | 'metrics' | 'tableCalculations' | 'additionalMetrics' | 'customDimensions'
  >;
  rows: ResultRow[];
  groupedSubtotals?: Record<string, Record<string, number>[]>;
  options: {
    maxColumns: number;
  };
  getField: (fieldId: string) => ItemsMap[string] | undefined;
  getFieldLabel: (fieldId: string) => string | undefined;
}): PivotData;

function convertSqlPivotedRowsToPivotData(args: {
  rows: ResultRow[];
  pivotDetails: NonNullable<ReadyQueryResultsPage['pivotDetails']>;
  pivotConfig: Pick<
    PivotConfig,
    'rowTotals' | 'columnTotals' | 'metricsAsRows' | 'hiddenMetricFieldIds' | 'columnOrder'
  >;
  getField: (fieldId: string) => ItemsMap[string] | undefined;
  getFieldLabel: (fieldId: string) => string | undefined;
  groupedSubtotals?: Record<string, Record<string, number>[]>;
}): PivotData;

function pivotResultsAsCsv(args: {
  pivotConfig: PivotConfig;
  pivotDetails: ReadyQueryResultsPage['pivotDetails'];
  rows: ResultRow[];
  itemMap: ItemsMap;
  metricQuery: MetricQuery;
  customLabels: Record<string, string> | undefined;
  onlyRaw: boolean;
  maxColumnLimit: number;
  undefinedCharacter?: string;
}): string[][];

function getPivotConfig(
  savedChart: Pick<CreateSavedChartVersion, 'chartConfig' | 'pivotConfig' | 'tableConfig'>
): PivotConfig | undefined;

function derivePivotConfigurationFromChart(
  savedChart: Pick<SavedChartDAO, 'chartConfig' | 'pivotConfig'>,
  metricQuery: MetricQuery,
  fields: ItemsMap
): PivotConfiguration | undefined;

function normalizeIndexColumns(
  indexColumn: PivotConfiguration['indexColumn']
): PivotIndexColum[];

function getFirstIndexColumns(
  indexColumn: PivotConfiguration['indexColumn']
): PivotIndexColum | undefined;

pivotQueryResults: Transforms regular query results into a pivot table format. This is the main pivot transformation function that reorganizes flat rows into a cross-tabulated structure with configurable row/column headers and aggregated values.

convertSqlPivotedRowsToPivotData: Converts results that are already pivoted at the SQL level into the PivotData format. Used when the database performs the pivot operation.

pivotResultsAsCsv: Exports pivoted results as CSV format (2D string array). Handles both client-side and SQL-side pivoted data.

getPivotConfig: Extracts pivot configuration from a saved chart, supporting both table and cartesian chart types.

derivePivotConfigurationFromChart: Derives a complete PivotConfiguration from a chart's configuration and metric query, including sort orders and column grouping.

normalizeIndexColumns: Normalizes index column specification to always be an array format.

getFirstIndexColumns: Returns the first index column from a pivot configuration.

Example:

import {
  pivotQueryResults,
  getPivotConfig,
  derivePivotConfigurationFromChart,
  normalizeIndexColumns,
} from '@lightdash/common';

// Get pivot config from saved chart
const pivotConfig = getPivotConfig(savedChart);

if (pivotConfig) {
  // Transform results to pivot format
  const pivotData = pivotQueryResults({
    pivotConfig,
    metricQuery,
    rows: queryResults,
    options: { maxColumns: 60 },
    getField: (fieldId) => itemsMap[fieldId],
    getFieldLabel: (fieldId) => itemsMap[fieldId]?.label || fieldId,
  });

  console.log('Pivot data rows:', pivotData.rowsCount);
  console.log('Pivot data cells:', pivotData.cellsCount);
}

// Derive configuration for SQL-side pivoting
const pivotConfiguration = derivePivotConfigurationFromChart(
  savedChart,
  metricQuery,
  itemsMap
);

if (pivotConfiguration) {
  // Normalize index columns to array format
  const indexColumns = normalizeIndexColumns(pivotConfiguration.indexColumn);
  console.log('Index columns:', indexColumns.map(c => c.reference));
}

Pivot Utility Functions

isPivotReferenceWithValues

Checks if a pivot reference contains pivot values.

function isPivotReferenceWithValues(
  pivotReference?: PivotReference
): pivotReference is Required<PivotReference>;

Example:

import { isPivotReferenceWithValues } from '@lightdash/common';

if (isPivotReferenceWithValues(pivotReference)) {
  // pivotReference.pivotValues is defined
  console.log('Pivot values:', pivotReference.pivotValues);
}

Pivot Chart Integration

Series with Pivot

interface Series {
  encode: {
    xRef: FieldId;
    yRef: FieldId;
    x?: number;
    y?: number;
  };
  type: CartesianSeriesType;
  // ... other properties
  pivot?: PivotReference;
}

When a series has a pivot reference, the yRef represents a generated pivot column.

Example:

const series: Series = {
  encode: {
    xRef: 'orders.order_date',
    yRef: 'orders.revenue_North', // Generated pivot column
  },
  type: CartesianSeriesType.LINE,
  pivot: {
    field: 'orders.region',
    pivotValues: [
      { field: 'orders.region', value: 'North' },
    ],
  },
};

derivePivotConfigurationFromChart

Derives pivot configuration from a saved chart's configuration and metric query, enabling consistent pivoting across all chart types.

/**
 * Derives pivot configuration from a saved chart's configuration and metric query
 * This enables consistent pivoting across all chart types
 * @param savedChart - The saved chart with chartConfig and optional pivotConfig
 * @param metricQuery - The metric query associated with the chart
 * @param fields - ItemsMap containing all available fields for type information
 * @returns PivotConfiguration if the chart supports pivoting, undefined otherwise
 */
function derivePivotConfigurationFromChart(
  savedChart: Pick<SavedChartDAO, 'chartConfig' | 'pivotConfig'>,
  metricQuery: MetricQuery,
  fields: ItemsMap
): PivotConfiguration | undefined;

This function analyzes a chart's configuration and automatically creates the appropriate pivot configuration. It handles:

Table charts: Uses pivotConfig.columns as group-by columns
Cartesian charts: Uses pivotConfig.columns as group-by and layout.yField as values
Other chart types: Returns undefined (pie, funnel, gauge, etc. don't support pivoting)

The function performs validation to ensure:

At least one index column exists
At least one value column exists
At least one group-by column exists
No overlap between index and group-by columns

Example:

import {
  derivePivotConfigurationFromChart,
  type SavedChart,
  type MetricQuery,
  type ItemsMap,
} from '@lightdash/common';

// Table chart with pivot configuration
const tableChart: SavedChart = {
  chartConfig: {
    type: ChartType.TABLE,
    // ... table config
  },
  pivotConfig: {
    columns: ['orders.region'],  // These become group-by columns
    metricsAsRows: false,
  },
};

const metricQuery: MetricQuery = {
  exploreName: 'orders',
  dimensions: ['orders.order_date', 'orders.region'],
  metrics: ['orders.total_revenue', 'orders.order_count'],
  sorts: [{ fieldId: 'orders.total_revenue', descending: true }],
};

const fields: ItemsMap = {
  'orders.order_date': { type: DimensionType.DATE, /* ... */ },
  'orders.region': { type: DimensionType.STRING, /* ... */ },
  'orders.total_revenue': { type: MetricType.NUMBER, /* ... */ },
  'orders.order_count': { type: MetricType.NUMBER, /* ... */ },
};

// Derive pivot configuration
const pivotConfig = derivePivotConfigurationFromChart(
  tableChart,
  metricQuery,
  fields
);

// Result:
// {
//   indexColumn: [
//     { reference: 'orders.order_date', type: 'time' }
//   ],
//   valuesColumns: [
//     { reference: 'orders.total_revenue', aggregation: 'ANY' },
//     { reference: 'orders.order_count', aggregation: 'ANY' }
//   ],
//   groupByColumns: [
//     { reference: 'orders.region' }
//   ],
//   sortBy: [
//     { reference: 'orders.total_revenue', direction: 'DESC' }
//   ]
// }

// Cartesian chart with pivot
const cartesianChart: SavedChart = {
  chartConfig: {
    type: ChartType.CARTESIAN,
    config: {
      layout: {
        xField: ['orders.order_date'],
        yField: ['orders.total_revenue', 'orders.order_count'],
      },
      eChartsConfig: {
        series: [/* ... */],
      },
    },
  },
  pivotConfig: {
    columns: ['orders.region'],
  },
};

const cartesianPivotConfig = derivePivotConfigurationFromChart(
  cartesianChart,
  metricQuery,
  fields
);
// Returns similar PivotConfiguration with yField as valuesColumns

// Chart type that doesn't support pivoting
const pieChart: SavedChart = {
  chartConfig: {
    type: ChartType.PIE,
    config: { /* ... */ },
  },
  pivotConfig: undefined,
};

const piePivotConfig = derivePivotConfigurationFromChart(
  pieChart,
  metricQuery,
  fields
);
// Returns undefined (pie charts don't support pivoting)

Usage with query service:

import {
  derivePivotConfigurationFromChart,
  type AsyncQueryService,
} from '@lightdash/common';

// In your query execution logic
const pivotConfiguration = derivePivotConfigurationFromChart(
  savedChart,
  metricQuery,
  itemsMap
);

if (pivotConfiguration) {
  // Execute pivoted query
  const results = await queryService.runPivotedQuery({
    ...metricQuery,
    pivotConfiguration,
  });
} else {
  // Execute regular query
  const results = await queryService.runQuery(metricQuery);
}

Complete Pivot Example

import {
  getPivotConfig,
  transformToPivotTable,
  type MetricQuery,
  type PivotConfig,
} from '@lightdash/common';

// Query with multiple dimensions and metrics
const metricQuery: MetricQuery = {
  exploreName: 'orders',
  dimensions: [
    'orders.order_date',   // Will be rows
    'orders.region',       // Will be pivot columns
  ],
  metrics: [
    'orders.total_revenue',
    'orders.order_count',
  ],
  sorts: [
    { fieldId: 'orders.order_date', descending: false },
  ],
};

// Define pivot configuration
const pivotConfig: PivotConfig = {
  pivotDimensions: ['orders.region'],   // Region becomes columns
  metricsAsRows: false,                 // Metrics as columns
  columnTotals: true,
  rowTotals: true,
};

// Execute query
const response = await runQuery(metricQuery);

// Transform to pivot format
const { headers, rows: pivotRows } = transformToPivotTable(
  response.rows,
  pivotConfig,
  itemsMap
);

// Render pivot table
console.log('Headers:', headers);
// ['Date', 'North', 'South', 'East', 'West']

pivotRows.forEach(row => {
  console.log(
    row['orders.order_date'].formatted,
    headers.slice(1).map(h => row[h]?.formatted || '-').join(' | ')
  );
});

// Output:
// 2024-01-01 | $1,000 | $1,500 | $2,000 | $1,200
// 2024-01-02 | $1,100 | $1,600 | $2,100 | $1,300
// ...

Pivot in Charts

Cartesian Chart with Pivot

When using pivot with cartesian charts, each pivot value becomes a separate series.

import {
  type CartesianChart,
  type PivotConfig,
  CartesianSeriesType,
} from '@lightdash/common';

const chartConfig: CartesianChart = {
  layout: {
    xField: 'orders.order_date',
    yField: ['orders.total_revenue'],
  },
  eChartsConfig: {
    series: [
      // Series for North region
      {
        encode: {
          xRef: 'orders.order_date',
          yRef: 'orders.total_revenue_North',
        },
        type: CartesianSeriesType.LINE,
        name: 'North',
        pivot: {
          field: 'orders.region',
          pivotValues: [{ field: 'orders.region', value: 'North' }],
        },
      },
      // Series for South region
      {
        encode: {
          xRef: 'orders.order_date',
          yRef: 'orders.total_revenue_South',
        },
        type: CartesianSeriesType.LINE,
        name: 'South',
        pivot: {
          field: 'orders.region',
          pivotValues: [{ field: 'orders.region', value: 'South' }],
        },
      },
      // ... more series for other regions
    ],
  },
};

const pivotConfig: PivotConfig = {
  pivotDimensions: ['orders.region'],
  metricsAsRows: false,
};

// This creates a line chart with one line per region

Pivot Values Columns

Generated columns in pivot results have special column definitions:

import { type PivotValuesColumn } from '@lightdash/common';

const pivotValuesColumns: Record<string, PivotValuesColumn> = {
  'orders.total_revenue_North': {
    reference: 'orders.total_revenue_North',
    label: 'Total Revenue (North)',
    pivotReference: {
      field: 'orders.region',
      pivotValues: [
        { field: 'orders.region', value: 'North' },
      ],
    },
  },
  'orders.total_revenue_South': {
    reference: 'orders.total_revenue_South',
    label: 'Total Revenue (South)',
    pivotReference: {
      field: 'orders.region',
      pivotValues: [
        { field: 'orders.region', value: 'South' },
      ],
    },
  },
};

// Use when formatting values
const northRevenueColumn = pivotValuesColumns['orders.total_revenue_North'];
console.log(northRevenueColumn.label); // "Total Revenue (North)"

Multi-Dimensional Pivots

You can pivot on multiple dimensions to create hierarchical column headers:

const multiDimensionPivot: PivotConfig = {
  pivotDimensions: [
    'orders.region',      // First level: Region
    'orders.quarter',     // Second level: Quarter
  ],
  metricsAsRows: false,
  columnTotals: false,
  rowTotals: false,
};

// Results in hierarchical columns:
//                    | North      | North      | South      | South      |
//                    | Q1    | Q2 | Q1    | Q2 | Q1    | Q2 | Q1    | Q2 |
// Electronics        | 1000  | ... | ... | ... | ... | ... | ... | ... |
// Clothing           | 800   | ... | ... | ... | ... | ... | ... | ... |

Additional Pivot Functions

derivePivotConfigurationFromChart

Derives a pivot configuration from a saved chart's configuration and metric query. This function analyzes chart configuration to automatically determine the appropriate pivot settings.

/**
 * Derives pivot configuration from a saved chart and metric query
 * Analyzes chart type, field layout, and query structure to generate PivotConfiguration
 * @param savedChart - The saved chart DAO
 * @param metricQuery - The metric query
 * @param itemsMap - Map of all queryable items
 * @returns PivotConfiguration or undefined if pivot not applicable
 */
function derivePivotConfigurationFromChart(
  savedChart: SavedChartDAO,
  metricQuery: MetricQuery,
  itemsMap: ItemsMap
): PivotConfiguration | undefined;

Example:

import { derivePivotConfigurationFromChart } from '@lightdash/common';

// Automatically derive pivot configuration from a chart
const pivotConfig = derivePivotConfigurationFromChart(
  savedChart,
  metricQuery,
  itemsMap
);

if (pivotConfig) {
  console.log('Chart requires pivoting');
  console.log(`Index columns: ${pivotConfig.indexColumn}`);
  console.log(`Group by columns: ${pivotConfig.groupByColumns?.length}`);
}

Pivot Utility Functions

Additional utilities from the pivot/utils module:

/**
 * Normalizes index columns to array format
 * @param indexColumn - Single column or array of columns
 * @returns Array of index columns
 */
function normalizeIndexColumns(
  indexColumn: PivotIndexColum | PivotIndexColum[] | undefined
): PivotIndexColum[];

/**
 * Gets unique values for a pivot dimension from query results
 * @param results - Query results
 * @param dimensionId - The dimension field ID
 * @returns Array of unique values
 */
function getPivotDimensionValues(
  results: ResultRow[],
  dimensionId: string
): unknown[];

/**
 * Generates pivot column reference from metric and pivot values
 * @param metricId - The metric field ID
 * @param pivotValues - The pivot dimension values
 * @returns Generated column reference
 */
function getPivotColumnReference(
  metricId: string,
  pivotValues: PivotReference['pivotValues']
): string;

Example:

import {
  normalizeIndexColumns,
  getPivotDimensionValues,
  getPivotColumnReference,
} from '@lightdash/common';

// Normalize index columns
const indexCols = normalizeIndexColumns(pivotConfig.indexColumn);

// Get unique pivot values
const regions = getPivotDimensionValues(results, 'orders.region');
// Returns: ['North', 'South', 'East', 'West']

// Generate pivot column reference
const colRef = getPivotColumnReference('orders.revenue', [
  { field: 'orders.region', value: 'North' }
]);
// Returns: 'orders.revenue_North'

Advanced Pivot Operations

Additional pivot operations for specialized use cases:

/**
 * Gets the first index column from the pivot configuration
 * @param indexColumn - Single column or array of columns from pivot configuration
 * @returns First index column or undefined if none exists
 */
function getFirstIndexColumns(
  indexColumn: PivotIndexColum | PivotIndexColum[] | undefined
): PivotIndexColum | undefined;

/**
 * Converts SQL-pivoted rows to pivot data format
 * Used when database performs the pivoting operation
 * @param args - Object with rows, field IDs, and pivot configuration
 * @returns Formatted PivotData
 */
function convertSqlPivotedRowsToPivotData(args: {
  rows: Record<string, unknown>[];
  indexFieldIds: string[];
  pivotFieldIds: string[];
  valueFieldIds: string[];
}): PivotData;

/**
 * Exports pivot results as CSV string
 * @param args - Object with pivot data and formatting options
 * @returns CSV formatted string
 */
function pivotResultsAsCsv(args: {
  pivotData: PivotData;
  onlyRaw?: boolean;
  columnSeparator?: string;
}): string;

Example:

import {
  getFirstIndexColumns,
  convertSqlPivotedRowsToPivotData,
  pivotResultsAsCsv,
} from '@lightdash/common';

// Get first index columns from pivot chart data
const firstIndexCols = getFirstIndexColumns(pivotChartData);

// Convert database-pivoted results
const pivotData = convertSqlPivotedRowsToPivotData({
  rows: sqlPivotedRows,
  indexFieldIds: ['orders.date'],
  pivotFieldIds: ['orders.region'],
  valueFieldIds: ['orders.revenue', 'orders.count'],
});

// Export to CSV
const csv = pivotResultsAsCsv({
  pivotData,
  onlyRaw: false,
  columnSeparator: ',',
});

// Save or download the CSV
console.log(csv);

Version

Tile

Files

pivot.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}docs/api/features/

Pivot Tables

Overview

Pivot Configuration Types

PivotConfig

PivotConfiguration

PivotData

PivotColumn

PivotReference

PivotValuesColumn

PivotIndexColum

ValuesColumn

GroupByColumn

Pivot Functions

Core Pivot Functions

Pivot Utility Functions

isPivotReferenceWithValues

Pivot Chart Integration

Series with Pivot

derivePivotConfigurationFromChart

Complete Pivot Example

Pivot in Charts

Cartesian Chart with Pivot

Pivot Values Columns

Multi-Dimensional Pivots

Additional Pivot Functions

derivePivotConfigurationFromChart

Pivot Utility Functions

Advanced Pivot Operations

pivot.mddocs/api/features/