Transposes one row of data into multiple rows. This function is a user-defined table-valued function (UDTF) that transposes columns into rows.
Limits
All columns that are used as
keysmust be placed before the columns that are to be transposed.Only one UDTF is allowed in a
SELECTstatement.
Syntax
trans_cols (<num_keys>, <key1>,<key2>,...,<col1>, <col2>,<col3>) as (<idx>, <key1>,<key2>,...,<col1>, <col2>)Parameters
num_keys: required. The value is a constant of the BIGINT type and must be greater than or equal to
0. This parameter specifies the number of columns that can be used as keys when you transpose one row into multiple rows.keys: required. This parameter specifies the columns that are used as keys when you transpose one row into multiple rows. The number of keys is specified by num_keys. If all columns are used as keys and the value of num_keys is equal to the total number of all columns, only one row is returned.
idx: required. This parameter specifies the ID of a row after the row is transposed.
cols: required. This parameter specifies the columns that you want to transpose into rows.
Return value
Transposed rows are returned. The new column name is specified by as. The first output column is the transposed subscript, which starts from 1. The data types of the columns that are used as keys remain unchanged, and the data types of other columns remain unchanged.
Examples
The t_table table contains the following data:
+----------+----------+------------+
| Login_id | Login_ip1 | Login_ip2 |
+----------+----------+------------+
| wangwangA | 192.168.0.1 | 192.168.0.2 |
+----------+----------+------------+
-- Execute the following SQL statement:
select trans_cols(1, login_id, login_ip1, login_ip2) as (idx, login_id, login_ip) from t_table;
-- The following result is returned:
idx login_id login_ip
1 wangwangA 192.168.0.1
2 wangwangA 192.168.0.2Related functions
For more information, see Other functions.