Pivot functions - Lightdash

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.

Pivot functions are only available when your query includes a pivoted dimension.

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

Compiled SQL

"column_index"

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

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)

Compiled SQL

For negative offsets (previous columns):

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.

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)

Compiled SQL

MAX(
  CASE WHEN "column_index" = 0
    THEN ${orders.total_revenue}
    ELSE NULL
  END
) OVER (PARTITION BY "row_index")

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})

Compiled SQL

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")

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})

Compiled SQL

ARRAY_AGG(${orders.total_revenue}) OVER (
  PARTITION BY "row_index"
  ORDER BY "column_index"
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

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)

Compiled SQL

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.

​pivot_column

​pivot_offset

​pivot_index

​pivot_where

​pivot_row

​pivot_offset_list

pivot_column

pivot_offset

pivot_index

pivot_where

pivot_row

pivot_offset_list