trans_cols is a user-defined table-valued function (UDTF) that converts a wide table into a narrow table by transposing multiple columns into rows. For example, a table with columns (login_id, login_ip1, login_ip2) becomes multiple rows of (login_id, login_ip), one per IP address column.
Limitations
-
Key columns must appear before the columns to transpose in the function call.
-
A
SELECTstatement can contain only one UDTF.
Syntax
trans_cols(<num_keys>, <key1>, <key2>, ..., <col1>, <col2>, <col3>) as (<idx>, <key1>, <key2>, ..., <col1>, <col2>)Parameters
| Parameter | Required | Description |
|---|---|---|
num_keys |
Yes | A BIGINT constant (≥ 0) that specifies how many leading columns to use as key columns. If num_keys equals the total number of columns, the function returns only one row. |
key1, key2, ... |
Yes | The key columns. The number of key columns must match num_keys. Key column values are repeated in each output row. |
col1, col2, ... |
Yes | The columns to transpose. Each column produces one output row per input row. |
idx |
Yes | The alias for the row index column in the output. |
Return value
The output table contains the following columns, in this order:
-
idx— An integer index that starts at 1 and increments by 1 for each transposed column. -
Key columns — One column per key, with values repeated across all output rows from the same input row. Data types are unchanged.
-
Value column — One column containing the value from the transposed column. Data types are unchanged.
Column names in the output come from the as alias list.
Examples
Create and populate t_table:
CREATE TABLE t_table (login_id STRING, login_ip1 STRING, login_ip2 STRING);
INSERT INTO t_table VALUES ('wangwangA', '192.168.0.1', '192.168.0.2');+----------+-------------+-------------+
| login_id | login_ip1 | login_ip2 |
+----------+-------------+-------------+
| wangwangA | 192.168.0.1 | 192.168.0.2 |
+----------+-------------+-------------+Use trans_cols with num_keys=1 to treat login_id as the key column and transpose login_ip1 and login_ip2 into separate rows:
SELECT trans_cols(1, login_id, login_ip1, login_ip2) AS (idx, login_id, login_ip)
FROM t_table;Result:
+-----+-----------+-------------+
| idx | login_id | login_ip |
+-----+-----------+-------------+
| 1 | wangwangA | 192.168.0.1 |
| 2 | wangwangA | 192.168.0.2 |
+-----+-----------+-------------+login_ip1 maps to idx=1 and login_ip2 maps to idx=2. The login_id key value repeats in both rows.
See also
For other built-in functions in MaxCompute, see Other functions.