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