Redshift多列逗号分隔值拆分多行SQL实现求助

Got it, let's solve this problem where you need to take comma-separated values in col2 and col3 of your table @tA, and expand them into every possible pair (a Cartesian product) tied to their corresponding col1 value.

Solution SQL #

Redshift provides split_to_array to turn comma-separated strings into arrays, and UNNEST to expand those arrays into rows. When you unnest multiple arrays together with a CROSS JOIN, it automatically creates the Cartesian product you need.

SELECT
    ta.col1,
    expanded_col2 AS col2,
    expanded_col3 AS col3
FROM
    @tA ta
CROSS JOIN UNNEST(
    split_to_array(ta.col2, ','),
    split_to_array(ta.col3, ',')
) AS expanded_values(expanded_col2, expanded_col3)
-- Optional: Add this filter if you need to exclude empty/null elements
WHERE
    expanded_col2 IS NOT NULL
    AND expanded_col3 IS NOT NULL;

How This Works #

Let's break down what each part does:

• split_to_array(ta.col2, ','): Converts the comma-separated string in col2 into an array (e.g., 'b2,b3' becomes ['b2','b3']).
• CROSS JOIN UNNEST(...): Takes the two arrays from col2 and col3, and expands them into rows. For every element in the col2 array, it pairs with every element in the col3 array—exactly the Cartesian product you're looking for.
• Result Matching: For rows where col2 or col3 only has one value (like a1 and a3), the unnest operation just returns a single row, matching your expected output.

Handling Edge Cases #

If your comma-separated values have extra spaces (e.g., 'b2, b3' instead of 'b2,b3'), you can clean them up with a regex replace inside the array split:

split_to_array(REGEXP_REPLACE(ta.col2, '\s+', ''), ',')

This removes all whitespace before splitting, ensuring clean array elements.

内容的提问来源于stack exchange，提问作者Durgesh panwar