TRANS_ARRAY

Limits

• All columns used as the key must be placed first. The columns to be transposed must follow.

• Only one UDTF can be used in a select statement, and no other columns can be included.

Command format

trans_array (<num_keys>, <separator>, <key1>,<key2>,…,<col1>,<col2>,<col3>) as (<key1>,<key2>,...,<col1>, <col2>)

Parameters

• num_keys: Required. A BIGINT constant. The value must be >=0. This parameter specifies the number of columns to use as the transpose key.

• separator: Required. A STRING constant. Specifies the separator used to split the string into elements. An error is returned if this parameter is empty.

• keys: Required. The columns to use as the key for transposition. The number of columns is specified by num_keys. If num_keys specifies that all columns are used as the key (that is, num_keys is equal to the total number of columns), only one row is returned.

• cols: Required. The columns that contain the arrays to transpose. All columns that follow the keys are treated as arrays for transposition. These columns must be of the STRING type and contain arrays in string format. For example, Hangzhou;Beijing;shanghai is an array where elements are separated by a semicolon (;).

Return value

The function returns the transposed rows. You can specify new column names using the as clause. The data types of the key columns remain unchanged, while all other columns are converted to the STRING type. The number of output rows is determined by the array with the most elements. Shorter arrays are padded with NULL values to match the length.

Examples

• Example 1: The t_table table contains the following data.

  +----------+----------+------------+
  | login_id | login_ip | login_time |
  +----------+----------+------------+
  | wangwangA | 192.168.0.1,192.168.0.2 | 20120101010000,20120102010000 |
  | wangwangB | 192.168.45.10,192.168.67.22,192.168.6.3 | 20120111010000,20120112010000,20120223080000 |
  +----------+----------+------------+
  --Execute the SQL statement.
  select trans_array(1, ",", login_id, login_ip, login_time) as (login_id,login_ip,login_time) from t_table;
  --The following result is returned.
  +----------+----------+------------+
  | login_id | login_ip | login_time |
  +----------+----------+------------+
  | wangwangB | 192.168.45.10 | 20120111010000 |
  | wangwangB | 192.168.67.22 | 20120112010000 |
  | wangwangB | 192.168.6.3 | 20120223080000 |
  | wangwangA | 192.168.0.1 | 20120101010000 |
  | wangwangA | 192.168.0.2 | 20120102010000 |
  +----------+----------+------------+
  
  --If the table contains the following data.
  Login_id LOGIN_IP LOGIN_TIME 
  wangwangA 192.168.0.1,192.168.0.2 20120101010000
  --Shorter arrays are padded with NULL values. 
  Login_id Login_ip Login_time 
  wangwangA 192.168.0.1 20120101010000
  wangwangA 192.168.0.2 NULL

• Example 2: The mf_fun_array_test_t table contains the following data.

  +------------+------------+------------+------------+
  | id         | name       | login_ip   | login_time |
  +------------+------------+------------+------------+
  | 1          | Tom        | 192.168.100.1,192.168.100.2 | 20211101010101,20211101010102 |
  | 2          | Jerry      | 192.168.100.3,192.168.100.4 | 20211101010103,20211101010104 |
  +------------+------------+------------+------------+
  
  --Use two keys, id and name, to transpose the arrays. Execute the SQL statement.
  select trans_array(2, ",", Id,Name, login_ip, login_time) as (Id,Name,login_ip,login_time) from mf_fun_array_test_t;
  --The following result is returned. The data is split and grouped by the keys, id and name.
  +------------+------------+------------+------------+
  | id         | name       | login_ip   | login_time |
  +------------+------------+------------+------------+
  | 1          | Tom        | 192.168.100.1 | 20211101010101 |
  | 1          | Tom        | 192.168.100.2 | 20211101010102 |
  | 2          | Jerry      | 192.168.100.3 | 20211101010103 |
  | 2          | Jerry      | 192.168.100.4 | 20211101010104 |
  +------------+------------+------------+------------+

Related functions

For more information about functions for other business scenarios, see Other functions.