stack table-valued generator function

  • Article

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

Separates expr1, …, exprN into numRows rows.

Syntax

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

Arguments

  • 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).

Returns

A set of numRows rows which includes max(1, (N/numRows)) columns produced by this function. An incomplete row is padded with NULLs.

By default, the produced columns are named col0, … col(n-1).

stack is equivalent to the VALUES clause.

  • Applies to: check marked yes Databricks Runtime 12.1 and earlier:

    stack can only be placed in the SELECT list as the root of an expression or following a LATERAL VIEW. When placing the function in the SELECT list there must be no other generator function in the same SELECT list or UNSUPPORTED_GENERATOR.MULTI_GENERATOR is raised.

  • Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above:

    Invocation from the LATERAL VIEW clause or the SELECT list is deprecated. Instead, invoke stack as a table_reference.

Examples

Applies to: check marked yes Databricks Runtime 12.1 and earlier:

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

> SELECT 'hello', stack(2, 1, 2, 3) AS (first, second), stack(2, 'a', 'b') AS (third) 'world';
 Error: UNSUPPORTED_GENERATOR.MULTI_GENERATOR

-- Equivalent usage of VALUES
> SELECT 'hello', s1.*, s2.*, 'world'
    FROM VALUES(1, 2), (3, NULL) AS s1(first, second),
         VALUES('a'), ('b') AS s2(third);
 hello  1   2       a   world
 hello  3   NULL    a   world
 hello  1   2       b   world
 hello  3   NULL    b   world

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above:

> SELECT 'hello', s.*, 'world'
    FROM stack(2, 1, 2, 3) AS s(first, second);
 hello 1  2    world
 hello 3  NULL world

> SELECT 'hello', s1.*, s2.*, 'world'
    FROM stack(2, 1, 2, 3) AS s1(first, second),
         stack(2, 'a', 'b') AS s2(third);
  hello 1  2    a  world
  hello 3  NULL a  world
  hello 1  2    b  world
  hello 3  NULL b  world