stack-MaxCompute(MaxCompute)-阿里云帮助中心

Splits expr1, ..., exprk into n rows. stack is equivalent to the VALUES clause — it turns a flat list of scalar expressions into a result set with rows and columns.

Unless you specify column aliases, the output uses the default column names col0, col1, and so on.

Syntax

stack(n, expr1, ..., exprk)

Parameters

Parameter	Required	Description
`n`	Yes	Number of rows to produce. Must be a positive integer.
`expr1, ..., exprk`	Yes	Expressions to split into rows. The total count `k` must be an integer multiple of `n`, so that each output row contains exactly `k/n` values. If `k` is not a multiple of `n`, an error is returned.

Return value

Returns n rows. The number of columns equals k / n. Default column names follow the pattern col0, col1, col2, and so on.

If the total number of expressions is not an integer multiple of n, stack returns an error. Make sure the expression count is divisible by n before running the query.

Examples

Split a list of integers into rows

-- Split 1, 2, 3, 4, 5, 6 into three rows.
select stack(3, 1, 2, 3, 4, 5, 6);
-- The following result is returned:
+------+------+
| col0 | col1 |
+------+------+
| 1    | 2    |
| 3    | 4    |
| 5    | 6    |
+------+------+

Specify column aliases

-- Split 'A',10,date '2015-01-01','B',20,date '2016-01-01' into two rows.
select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') as (col0,col1,col2);
-- The following result is returned:
+------+------+------+
| col0 | col1 | col2 |
+------+------+------+
| A    | 10   | 2015-01-01 |
| B    | 20   | 2016-01-01 |
+------+------+------+

Apply to each row of a source table

When the source table contains multiple rows, stack is called once per row.

-- Split the parameter group of a, b, c, and d into two rows. If the source table contains multiple rows, this function is called for each row.
select stack(2,a,b,c,d) as (col,value)
from values
    (1,1,2,3,4),
    (2,5,6,7,8),
    (3,9,10,11,12),
    (4,13,14,15,null)
as t(key,a,b,c,d);
-- The following result is returned:
+------+-------+
| col  | value |
+------+-------+
| 1    | 2     |
| 3    | 4     |
| 5    | 6     |
| 7    | 8     |
| 9    | 10    |
| 11   | 12    |
| 13   | 14    |
| 15   | NULL  |
+------+-------+

Use with LATERAL VIEW

-- Use this function with the LATERAL VIEW clause.
select tf.* from (select 0) t lateral view stack(2,'A',10,date '2015-01-01','B',20, date '2016-01-01') tf as col0,col1,col2;
-- The following result is returned:
+------+------+------+
| col0 | col1 | col2 |
+------+------+------+
| A    | 10   | 2015-01-01 |
| B    | 20   | 2016-01-01 |
+------+------+------+

Related functions

For more information, see Other functions.