MaxCompute:STACK

Syntax

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

Parameters

• n: required. The number of rows obtained after splitting.

• expr: required. The parameter that you want to split. expr1,... exprk must be of the INTEGER type, and the number of parameters must be an integer multiple of n. The parameter must be able to be split into n complete rows. Otherwise, an error is returned.

Return value

n rows with a specific number of columns are returned. The number of columns is equal to the number of parameters divided by n.

Examples

-- Split the parameter group of 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    |
+------+------+

-- 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 |
+------+------+------+

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