STACK

Description

The STACK function is a powerful data transformation tool that can stack multiple expressions into specified rows, generating new rows and columns. This function allows for flexible integration of different types of data during processing and can be used in conjunction with LATERAL VIEW to achieve more complex data operations.

Syntax

STACK(n, expr1, expr2, ..., exprK)

Parameter Description

• n: Integer, representing the number of rows to generate.
• expr1 ~ exprK: A list of expressions that are of the same type or can be implicitly converted to the same type.

Return Result

Returns a value of the same type as expr1 ~ exprK.

Usage Example

Example 1: Basic Usage

SELECT 'hello', STACK(2, 1, 2, 3) AS (first, second), 'world';
+---------+-------+--------+---------+
| 'hello' | first | second | 'world' |
+---------+-------+--------+---------+
| hello   | 1     | 2      | world   |
| hello   | 3     | null   | world   |
+---------+-------+--------+---------+

Example 2: Using with `` **

SELECT w, x, y FROM VALUES
  ('apple', 1, 2, 3, 4),
  ('banana', 11, 22, 33, 44),
  ('orange', 111, 222, 333, 444),
  ('dog', 1111, 2222, 3333, 4444) AS t(w, a, b, c, d)
LATERAL VIEW STACK(2, a, b, c) lv AS x, y;
+--------+------+------+
|   w    |  x   |  y   |
+--------+------+------+
| apple  | 1    | 2    |
| apple  | 3    | null |
| banana | 11   | 22   |
| banana | 33   | null |
| orange | 111  | 222  |
| orange | 333  | null |
| dog    | 1111 | 2222 |
| dog    | 3333 | null |
+--------+------+------+

Example 3: Implicit Type Conversion

SELECT STACK(2, 1, '2', 3) AS (col1, col2);
+------+------+
| col1 | col2 |
+------+------+
| 1    | 2    |
| 3    |      |
+------+------+