# Pivoting and Grouping

Rowsncolumns Spreadsheet supports advanced pivot table functionality powered by DuckDB, allowing you to create dynamic data summaries with row and column grouping, aggregations, and filtering.

## Features

* **Server-side pivot operations** using DuckDB for high performance
* **Row and column grouping** with multiple levels of nesting
* **Dynamic filtering** with multiple filter types
* **Sorting** by any field
* **Aggregation functions**: sum, count, avg, min, max, var, stddev, median, product
* **React hook** (`usePivot`) for easy integration
* **Multiple pivot tables** support with pivotId-based API
* **Interactive pivot editor** UI component

## Installation

The pivot functionality is available as a separate package:

{% tabs %}
{% tab title="npm" %}

```bash
npm install @rowsncolumns/pivot
```

{% endtab %}

{% tab title="yarn" %}

```bash
yarn add @rowsncolumns/pivot
```

{% endtab %}
{% endtabs %}

## Basic Usage

### Setting up the Pivot Hook

```typescript
import { usePivot } from "@rowsncolumns/pivot";
import type { PivotTable, SheetRange } from "@rowsncolumns/spreadsheet";
import { useState } from "react";

function MySpreadsheet() {
  const [pivotTables, setPivotTables] = useState<PivotTable[]>([]);
  const [sheetData, setSheetData] = useState<SheetData>();

  const {
    addRowPivot,
    addColumnPivot,
    addValue,
    executePivot,
    onRequestCreatePivotTable,
    onCreatePivotTable,
    isInitializing,
  } = usePivot({
    locale: 'en-US',
    pivotTables,
    activeCell,
    sheetId: activeSheetId,
    cellXfsRegistry,
    onChangeSheets,
    onChangePivotTables: setPivotTables,
    onChangeSheetData: setSheetData,
    onCreateNewSheet,
    createHistory,
    enqueueCalculation,
    onChangeActiveCell,
    getGridValues: (range: SheetRange) => {
      // Extract data from your spreadsheet
      return {
        headers: ["Product", "Region", "Sales", "Quantity"],
        rows: [
          ["Laptop", "North", 1000, 5],
          ["Phone", "South", 500, 10],
          ["Tablet", "East", 750, 7],
          // ...more data
        ],
      };
    },
  });

  // Check if database is initializing
  if (isInitializing) {
    return <div>Initializing pivot database...</div>;
  }

  return (
    // Your spreadsheet UI
  );
}
```

### Creating a Pivot Table

You can create pivot tables programmatically or through the UI:

```typescript
// Programmatic creation
await onCreatePivotTable({
  pivotId: crypto.randomUUID(), // Generate unique ID
  source: {
    sheetId: 1,
    startRowIndex: 1,
    endRowIndex: 100,
    startColumnIndex: 1,
    endColumnIndex: 10,
  },
  targetPosition: {
    rowIndex: 1,
    columnIndex: 12,
    sheetId: 1,
  },
  rows: [
    { field: "Region" }
  ],
  columns: [
    { field: "Product" }
  ],
  values: [
    { field: "Sales", aggFunc: "sum" }
  ],
});
```

### Adding Pivot Fields

```typescript
// Add a row grouping
await addRowPivot(pivotId, {
  field: "Region",
  displayName: "Region",
});

// Add a column grouping
await addColumnPivot(pivotId, {
  field: "Product",
  displayName: "Product",
});

// Add a value with aggregation
await addValue(pivotId, {
  field: "Sales",
  displayName: "Total Sales",
  aggFunc: "sum",
});

// Execute the pivot to update the spreadsheet
const result = await executePivot(pivotId);
```

## Understanding `getGridValues`

The `getGridValues` function is critical - it extracts data from your spreadsheet and formats it for pivoting:

```typescript
const getGridValues = useCallback(
  (range: SheetRange) => {
    let headers: (string | number | boolean)[] = [];
    const rows = [];
    
    for (
      let rowIndex = range.startRowIndex;
      rowIndex <= range.endRowIndex;
      rowIndex++
    ) {
      const row = [];
      const isHeader = rowIndex === range.startRowIndex;
      
      for (
        let columnIndex = range.startColumnIndex;
        columnIndex <= range.endColumnIndex;
        columnIndex++
      ) {
        const value = getEffectiveValue(range.sheetId, rowIndex, columnIndex) ?? "";
        row.push(value);
      }
      
      if (isHeader) {
        headers = row;
      } else {
        rows.push(row);
      }
    }

    return { headers, rows };
  },
  [getEffectiveValue]
);
```

## Using the Pivot Editor Component

The package includes a UI component for managing pivot tables:

```typescript
import { PivotEditor } from "@rowsncolumns/pivot";

// In your component
const activePivotTable = pivotTables.find(
  (table) => table.pivotId === activePivotId
);

return (
  <div>
    {activePivotTable ? (
      <PivotEditor
        pivotTable={activePivotTable}
        getGridValues={getGridValues}
        addColumnPivot={addColumnPivot}
        addRowPivot={addRowPivot}
        removePivotField={removePivotField}
        changeFieldOrder={changeFieldOrder}
        addValue={addValue}
        setAggregationFunction={setAggregationFunction}
        onRequestClose={onClosePivotSettings}
        updatePivotSourceRange={updatePivotSourceRange}
      />
    ) : null}
  </div>
);
```

## API Reference

### Core Operations

| Function          | Description                              | Parameters          |
| ----------------- | ---------------------------------------- | ------------------- |
| `executePivot`    | Execute pivot and update the spreadsheet | `(pivotId: string)` |
| `refreshPivot`    | Recalculate an existing pivot            | `(pivotId: string)` |
| `getPivotState`   | Get current pivot configuration          | `(pivotId: string)` |
| `getPivotManager` | Get the pivot manager instance           | `(pivotId: string)` |

### Field Management

| Function           | Description                             | Parameters                            |
| ------------------ | --------------------------------------- | ------------------------------------- |
| `addRowPivot`      | Add a field to the rows area            | `(pivotId, field: PivotField)`        |
| `addColumnPivot`   | Add a field to the columns area         | `(pivotId, field: PivotField)`        |
| `addValue`         | Add a field to the values area          | `(pivotId, field: PivotField)`        |
| `removePivotField` | Remove a field from rows/columns/values | `(pivotId, field, area)`              |
| `changeFieldOrder` | Reorder fields within an area           | `(pivotId, area, fromIndex, toIndex)` |
| `expandRowPivot`   | Expand a row group to show details      | `(pivotId, groupKeys: string[])`      |

### Filtering and Aggregation

| Function                 | Description                       | Parameters                      |
| ------------------------ | --------------------------------- | ------------------------------- |
| `addFilter`              | Add a filter to the pivot         | `(pivotId, field, filterValue)` |
| `removeFilter`           | Remove a filter                   | `(pivotId, field)`              |
| `setAggregationFunction` | Set aggregation for a value field | `(pivotId, field, aggFunc)`     |

### UI Integration

| Function                    | Description                   | Parameters                     |
| --------------------------- | ----------------------------- | ------------------------------ |
| `onRequestCreatePivotTable` | Trigger pivot creation dialog | `()`                           |
| `onCreatePivotTable`        | Create a new pivot table      | `(config: PivotTableConfig)`   |
| `onRequestEditPivotTable`   | Open pivot editor for a table | `(pivotId: string)`            |
| `onRequestDeletePivotTable` | Trigger delete confirmation   | `(pivotId: string)`            |
| `onClosePivotSettings`      | Close the pivot editor        | `()`                           |
| `updatePivotSourceRange`    | Update the source data range  | `(pivotId, range: SheetRange)` |

### Transform Functions

| Function                | Description                                   | Return Type                                  |
| ----------------------- | --------------------------------------------- | -------------------------------------------- |
| `transformPivotResults` | Convert pivot results to 2D array for display | `{ data: any[][], metadata: PivotMetadata }` |
| `transformChildRows`    | Get child rows for drill-down                 | `PivotRow[]`                                 |

## Aggregation Functions

The following aggregation functions are supported:

* **sum** - Sum of values
* **count** - Count of values
* **avg** - Average of values
* **min** - Minimum value
* **max** - Maximum value
* **var** - Variance
* **stddev** - Standard deviation
* **median** - Median value
* **product** - Product of values

## Complete Example

```typescript
import React, { useState, useCallback } from 'react';
import {
  SpreadsheetProvider,
  CanvasGrid,
  PivotTable,
} from "@rowsncolumns/spreadsheet";
import {
  usePivot,
  PivotEditor,
  NewPivotTableDialog,
  NewPivotTableEditor,
} from "@rowsncolumns/pivot";
import { useSpreadsheetState } from "@rowsncolumns/spreadsheet-state";

function SpreadsheetWithPivot() {
  const [pivotTables, setPivotTables] = useState<PivotTable[]>([]);
  
  const {
    activeCell,
    activeSheetId,
    sheets,
    sheetData,
    onChangeSheets,
    onChangeSheetData,
    onChangeBatch,
    getEffectiveValue,
    onCreateNewSheet,
    createHistory,
    enqueueCalculation,
    onChangeActiveCell,
    cellXfsRegistry,
  } = useSpreadsheetState({
    sheets: [],
    sheetData: {},
    onChangePivotTables: setPivotTables,
  });

  const getGridValues = useCallback(
    (range: SheetRange) => {
      let headers: (string | number | boolean)[] = [];
      const rows = [];
      
      for (
        let rowIndex = range.startRowIndex;
        rowIndex <= range.endRowIndex;
        rowIndex++
      ) {
        const row = [];
        for (
          let columnIndex = range.startColumnIndex;
          columnIndex <= range.endColumnIndex;
          columnIndex++
        ) {
          row.push(
            getEffectiveValue(range.sheetId, rowIndex, columnIndex) ?? ""
          );
        }
        
        if (rowIndex === range.startRowIndex) {
          headers = row;
        } else {
          rows.push(row);
        }
      }

      return { headers, rows };
    },
    [getEffectiveValue]
  );

  const {
    addRowPivot,
    addColumnPivot,
    addValue,
    executePivot,
    onRequestCreatePivotTable,
    onCreatePivotTable,
    onRequestEditPivotTable,
    activePivotId,
    onClosePivotSettings,
    removePivotField,
    changeFieldOrder,
    setAggregationFunction,
    updatePivotSourceRange,
  } = usePivot({
    locale: 'en-US',
    pivotTables,
    activeCell,
    sheetId: activeSheetId,
    cellXfsRegistry,
    onChangeSheets,
    onChangePivotTables: setPivotTables,
    onChangeSheetData,
    getGridValues,
    onCreateNewSheet,
    createHistory,
    enqueueCalculation,
    onChangeActiveCell,
  });

  const activePivotTable = pivotTables.find(
    (table) => table.pivotId === activePivotId
  );

  return (
    <SpreadsheetProvider>
      <CanvasGrid
        pivotTables={pivotTables}
        onRequestEditPivotTable={onRequestEditPivotTable}
        // ...other props
      />
      
      {/* Pivot Editor Sidebar */}
      {activePivotTable ? (
        <div className="pivot-editor-sidebar">
          <PivotEditor
            pivotTable={activePivotTable}
            getGridValues={getGridValues}
            addColumnPivot={addColumnPivot}
            addRowPivot={addRowPivot}
            removePivotField={removePivotField}
            changeFieldOrder={changeFieldOrder}
            addValue={addValue}
            setAggregationFunction={setAggregationFunction}
            onRequestClose={onClosePivotSettings}
            updatePivotSourceRange={updatePivotSourceRange}
          />
        </div>
      ) : null}

      {/* New Pivot Table Dialog */}
      <NewPivotTableDialog>
        <NewPivotTableEditor
          sheetId={activeSheetId}
          onSubmit={onCreatePivotTable}
        />
      </NewPivotTableDialog>
    </SpreadsheetProvider>
  );
}
```

## Advanced Features

### Formatting Pivot Results

You can apply custom formatting to pivot table results:

```typescript
import { createPivotTableFormats } from "@rowsncolumns/pivot";

const result = await executePivot(pivotId);

if (result) {
  const { data, metadata } = transformPivotResults(pivotId, result);
  
  // Create formats for the pivot table
  const formats = createPivotTableFormats(data, metadata, {
    headerBackgroundColor: { theme: 4, tint: 0.8 },
    headerTextColor: { theme: 1 },
  });

  // Apply the data and formats to the sheet
  onChangeBatch(activeSheetId, pivotRange, data, formats);
}
```

### Drill-Down Support

Expand row groups to see detailed data:

```typescript
// Expand a specific row group
const childRows = await expandRowPivot(pivotId, ["North", "Laptop"]);

// Transform child rows for display
const transformedChildren = transformChildRows(
  pivotId, 
  childRows, 
  ["North", "Laptop"]
);
```

## Database Initialization

The `usePivot` hook handles DuckDB initialization automatically:

1. **Auto-initialization** (default): The hook initializes DuckDB on first use
2. **Manual initialization**: Optionally provide a pre-initialized database
3. **Singleton pattern**: The default database is shared across pivot instances

```typescript
// Auto-initialization (recommended)
const { ... } = usePivot({
  pivotTables,
  // No database prop needed
});

// Manual initialization
import { initializeDatabase } from "@rowsncolumns/pivot";

const db = await initializeDatabase();
const { ... } = usePivot({
  pivotTables,
  database: db,
});
```

Check initialization status:

```typescript
const { isInitializing } = usePivot({ ... });

if (isInitializing) {
  return <LoadingIndicator />;
}
```

## PivotTable Type Structure

```typescript
interface PivotTable {
  pivotId: string;              // Unique identifier
  source: SheetRange;           // Source data range
  targetPosition: {             // Where to place results
    rowIndex: number;
    columnIndex: number;
    sheetId: number;
  };
  rows: PivotGroup[];           // Row groupings
  columns: PivotGroup[];        // Column groupings
  values: PivotValue[];         // Aggregation values
  filters?: Record<string, any>;  // Filter configuration
  sortModel?: Array<{           // Sort configuration
    field: string;
    sort: "asc" | "desc";
  }>;
}
```

## Performance Considerations

* Pivot operations run in DuckDB for optimal performance
* Large datasets are handled efficiently through SQL aggregation
* Results are cached and only recalculated when needed
* Use `refreshPivot` to manually trigger recalculation when source data changes

## Troubleshooting

### Pivot table not updating

Make sure you call `executePivot` after making configuration changes:

```typescript
await addRowPivot(pivotId, { field: "Region" });
await addValue(pivotId, { field: "Sales", aggFunc: "sum" });
await executePivot(pivotId); // Don't forget this!
```

### Database initialization issues

Check the `isInitializing` state before using pivot features:

```typescript
const { isInitializing } = usePivot({ ... });

if (isInitializing) {
  // Show loading state
}
```

### Source data not updating

Ensure your `getGridValues` function is using the latest spreadsheet data and is properly memoized with dependencies.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.rowsncolumns.app/configuration/features/pivoting-and-grouping.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.