This topic describes how to use the CROSS JOIN statement and the UNNEST clause to convert values that are separated by commas (,) in a row into multiple rows.
Convert an array into multiple rows
Convert the data of a column into an array and then convert the array into multiple rows. Example:
#### Create a database.
CREATE DATABASE mydb;
USE mydb;
### Create a table.
CREATE TABLE test(
userid INT
,user_name VARCHAR
,product VARCHAR
) distributed by hash(userid);
### Insert two rows of test data into the table.
INSERT INTO test VALUES
(1,'aaa','cat,mat,bat'),(2,'bbb','dog,pog,fog');
#### Convert the product column into multiple rows, save the rows in the col column of the temp_table table, and then query the data of the col column.
SELECT userid, col
FROM (select userid, split(product,',') as numbers_array from test)
CROSS JOIN UNNEST(numbers_array) as temp_table(col);
### Query result:
userid col
1 cat
1 mat
1 bat
2 dog
2 pog
2 fog