> ## Documentation Index
> Fetch the complete documentation index at: https://lightdash-mintlify-9d6f9427.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Pivot functions

> Built-in functions for accessing values across pivot columns in your table calculations.

Pivot functions let you work with values across pivot columns in your results table. When you pivot a dimension in Lightdash, the values of that dimension become separate columns — pivot functions give you a way to reference and aggregate across those columns.

<Note>
  Pivot functions are only available when your query includes a pivoted dimension.
</Note>

## pivot\_column

Returns the 0-based index of the current pivot column.

```
pivot_column()
```

**Parameters:** None

**Example**

Use the column index to apply different logic per pivot column:

```
CASE WHEN pivot_column() = 0 THEN 'First' ELSE 'Other' END
```

<Accordion title="Compiled SQL">
  ```sql theme={null}
  "column_index"
  ```

  The pivot column index is a field in the underlying pivoted results, so no window function is needed.
</Accordion>

***

## pivot\_offset

Returns the value of an expression from a pivot column at a relative offset from the current column.

```
pivot_offset(expression, columnOffset)
```

| Parameter      | Type                               | Description                                                                                           |
| :------------- | :--------------------------------- | :---------------------------------------------------------------------------------------------------- |
| `expression`   | column reference or SQL expression | The expression to evaluate                                                                            |
| `columnOffset` | integer                            | Number of columns to offset. Negative = previous columns, positive = next columns, 0 = current column |

Returns `NULL` if the target column is not adjacent (e.g., if intermediate columns were filtered out).

**Example**

Compare the current pivot column's revenue against the previous pivot column:

```
${orders.total_revenue} - pivot_offset(${orders.total_revenue}, -1)
```

<Accordion title="Compiled SQL">
  For negative offsets (previous columns):

  ```sql theme={null}
  CASE WHEN LAG("column_index", 1) OVER (
      PARTITION BY "row_index" ORDER BY "column_index"
    ) = "column_index" + (-1)
    THEN LAG(${orders.total_revenue}, 1) OVER (
      PARTITION BY "row_index" ORDER BY "column_index"
    )
    ELSE NULL
  END
  ```

  For positive offsets (next columns), `LEAD` is used instead of `LAG`.

  For an offset of 0, the expression is returned directly with no window function.

  Each call includes an adjacency guard — a `CASE WHEN` check that verifies the target column is actually adjacent. This prevents incorrect values when pivot columns have been filtered out and are non-contiguous.
</Accordion>

***

## pivot\_index

Returns the value of an expression from a specific pivot column by its 0-based index.

```
pivot_index(expression, pivotIndex)
```

| Parameter    | Type                               | Description                    |
| :----------- | :--------------------------------- | :----------------------------- |
| `expression` | column reference or SQL expression | The expression to evaluate     |
| `pivotIndex` | integer (≥ 0)                      | The 0-based pivot column index |

**Example**

Compare every pivot column's revenue against the first pivot column's revenue:

```
${orders.total_revenue} / pivot_index(${orders.total_revenue}, 0)
```

<Accordion title="Compiled SQL">
  ```sql theme={null}
  MAX(
    CASE WHEN "column_index" = 0
      THEN ${orders.total_revenue}
      ELSE NULL
    END
  ) OVER (PARTITION BY "row_index")
  ```
</Accordion>

***

## pivot\_where

Finds the first pivot column where a condition is true and returns a value from that column.

```
pivot_where(selectExpression, valueExpression)
```

| Parameter          | Type                               | Description                                       |
| :----------------- | :--------------------------------- | :------------------------------------------------ |
| `selectExpression` | SQL boolean expression             | Condition to evaluate for each pivot column       |
| `valueExpression`  | column reference or SQL expression | The expression to return from the matching column |

If multiple columns match, the value from the column with the lowest index is returned.

**Example**

Find the revenue from the first pivot column where the count exceeds 100:

```
pivot_where(${orders.count} > 100, ${orders.total_revenue})
```

<Accordion title="Compiled SQL">
  ```sql theme={null}
  MAX(
    CASE WHEN "column_index" = (
      SELECT MIN("column_index")
      FROM (
        SELECT "column_index",
          ${orders.count} > 100 AS condition
        FROM DUAL
      )
      WHERE condition = TRUE
    )
    THEN ${orders.total_revenue}
    ELSE NULL
    END
  ) OVER (PARTITION BY "row_index")
  ```
</Accordion>

***

## pivot\_row

Returns an array of all values across the pivot columns for the current row.

```
pivot_row(expression)
```

| Parameter    | Type                               | Description                                      |
| :----------- | :--------------------------------- | :----------------------------------------------- |
| `expression` | column reference or SQL expression | The expression to evaluate for each pivot column |

**Example**

Get all pivoted revenue values for the current row:

```
pivot_row(${orders.total_revenue})
```

<Accordion title="Compiled SQL">
  ```sql theme={null}
  ARRAY_AGG(${orders.total_revenue}) OVER (
    PARTITION BY "row_index"
    ORDER BY "column_index"
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
  ```
</Accordion>

***

## pivot\_offset\_list

Returns an array of values from consecutive pivot columns starting at a relative offset from the current column.

```
pivot_offset_list(expression, columnOffset, numValues)
```

| Parameter      | Type                               | Description                                                                                      |
| :------------- | :--------------------------------- | :----------------------------------------------------------------------------------------------- |
| `expression`   | column reference or SQL expression | The expression to evaluate                                                                       |
| `columnOffset` | integer                            | Starting column offset. Negative = previous columns, positive = next columns, 0 = current column |
| `numValues`    | integer                            | Number of consecutive pivot columns to include                                                   |

Values are returned as `NULL` when the offset points to a non-adjacent pivot column (e.g., if columns were filtered out).

**Example**

Get the current and two previous pivot column values:

```
pivot_offset_list(${orders.total_revenue}, -2, 3)
```

<Accordion title="Compiled SQL">
  ```sql theme={null}
  ARRAY[
    CASE WHEN LAG("column_index", 2) OVER (
        PARTITION BY "row_index" ORDER BY "column_index"
      ) = "column_index" + (-2)
      THEN LAG(${orders.total_revenue}, 2) OVER (
        PARTITION BY "row_index" ORDER BY "column_index"
      )
      ELSE NULL
    END,
    CASE WHEN LAG("column_index", 1) OVER (
        PARTITION BY "row_index" ORDER BY "column_index"
      ) = "column_index" + (-1)
      THEN LAG(${orders.total_revenue}, 1) OVER (
        PARTITION BY "row_index" ORDER BY "column_index"
      )
      ELSE NULL
    END,
    ${orders.total_revenue}
  ]
  ```

  Each element includes an adjacency guard — a `CASE WHEN` check that verifies the target column is actually adjacent. This prevents incorrect values when pivot columns have been filtered out and are non-contiguous.
</Accordion>