stack table-valued generator function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Separates expr1, …, exprN into numRows rows.


stack(numRows, expr1 [, ...] )


  • numRows: An INTEGER literal greater than 0 specifying the number of rows produced.

  • exprN: An expression of any type. The type of any exprN must match the type of expr(N+numRows).


A set of numRows rows which includes all other columns in the select list and max(1, (N/numRows)) columns produced by this function. An incomplete row is padded with NULL.

By default the produced columns are named col0, col(n-1). The column aliases can be specified using for example, AS (myCol1, .. myColn).

You can place stack only in the select list or a LATERAL VIEW. When placing the function in the select list there must be no other generator function in the same select list.


SELECT 'hello', stack(2, 1, 2, 3) AS (first, second), 'world';
-- hello 1  2    world
-- hello 3  NULL world