This topic describes how to use the CROSS JOIN statement to convert a column to rows. The statement can convert tuples in a column to rows.

Convert an array to rows

Convert a column to an array and then convert the array to rows. The following code block provides an 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.
INSERT INTO test VALUES
(1,'aaa','cat,mat,bat'),(2,'bbb','dog,pog,fog');

####Query the data. Convert the product column to rows. temp_table is the temporary table name and can be changed. col is the column name returned.
SELECT userid, col
FROM (select userid, split(product,',') as numbers_array from test)
CROSS JOIN UNNEST(numbers_array) as temp_table(col);

###The query result.
userid col
1	cat
1	mat
1	bat
2	dog
2	pog
2	fog