stack
table-valued generator function
Applies to: Databricks SQL Databricks Runtime
Separates expr1
, …, exprN
into numRows
rows.
Arguments
numRows
: AnINTEGER
literal greater than 0 specifying the number of rows produced.exprN
: An expression of any type. The type of anyexprN
must match the type ofexpr(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 NULL
s.
By default, the produced columns are named col0, … col(n-1)
.
stack
is equivalent to the `VALUES` clause.
Applies to: Databricks Runtime 12.1 and earlier:
stack
can only be placed in theSELECT
list as the root of an expression or following a LATERAL VIEW. When placing the function in theSELECT
list there must be no other generator function in the sameSELECT
list or UNSUPPORTED_GENERATOR.MULTI_GENERATOR is raised.Applies to: Databricks SQL Databricks Runtime 12.2 LTS and above:
Invocation from the LATERAL VIEW clause or the
SELECT
list is deprecated. Instead, invokestack
as a table_reference.
Examples
Applies to: 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: Databricks SQL 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