AnalyticDB:CROSS JOIN

How it works

1. Apply split(column, ',') to convert the comma-separated string into an array.

2. Use CROSS JOIN UNNEST(array_column) AS alias(col) to expand the array. Each element becomes a new row, paired with the original row's other columns.

The alias AS temp_table(col) defines the output column name: temp_table is the table alias for the unnested result, and col is the name of the column that holds each expanded value. The output column type matches the element type of the input array — in this case, VARCHAR.

Convert a column into multiple rows

The following example creates a table with a comma-separated product column, then expands each product into its own row.

-- 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');

-- Expand the product column into multiple rows
SELECT userid, col
FROM (select userid, split(product,',') as numbers_array from test)
CROSS JOIN UNNEST(numbers_array) as temp_table(col);

The query returns: