How to split values to multiple rows in SQL?

Problem

How to split values to multiple rows in SQL?

Input

To illustrate this problem, let’s create a sample input table named “Sales” with some data. The table will have two columns: “Date” and “Amount.”

id	vals
1	value1,value2,value3
2	value4,value5
3	value6

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

id	split_value
1	value1
2	value4
3	value6
1	value2
2	value5
1	value3

Solution 1:

Using Recursive SQL

You can split these comma-separated values into multiple rows using a combination of SQL functions. In MySQL, you can use a recursive Common Table Expression (CTE) to achieve this.

Here’s the SQL query to split the values:

    WITH RECURSIVE SplitValues AS (
        SELECT
            id,
            SUBSTRING_INDEX(vals, ',', 1) AS split_value,
            IF(LOCATE(',', vals) > 0, SUBSTRING(vals, LOCATE(',', vals) + 1), NULL) AS remaining_values
        FROM
            original_table
        UNION ALL
        SELECT
            id,
            SUBSTRING_INDEX(remaining_values, ',', 1) AS split_value,
            IF(LOCATE(',', remaining_values) > 0, SUBSTRING(remaining_values, LOCATE(',', remaining_values) + 1), NULL)
        FROM
            SplitValues
        WHERE
            remaining_values IS NOT NULL
    )
    SELECT
        id,
        split_value
    FROM
        SplitValues;

Solution 2:

By using a custom created SQL procedure

    CREATE PROCEDURE SplitAndInsert()
    BEGIN
        DECLARE done INT DEFAULT 0;
        DECLARE id INT;
        DECLARE value VARCHAR(255);
        DECLARE cur CURSOR FOR
            SELECT id, vals FROM original_table;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        CREATE TABLE IF NOT EXISTS split_table (
            id INT,
            split_value VARCHAR(255)
        );

        OPEN cur;

        read_loop: LOOP
            FETCH cur INTO id, value;
            IF done THEN
                LEAVE read_loop;
            END IF;

            SET @pos = 1;
            SET @len = LENGTH(value);

            WHILE @pos <= @len DO
                SET @delimiterPos = LOCATE(',', value, @pos);
                IF @delimiterPos = 0 THEN
                    SET @delimiterPos = @len + 1;
                END IF;

                INSERT INTO split_table (id, split_value)
                VALUES (id, SUBSTRING(value, @pos, @delimiterPos - @pos));

                SET @pos = @delimiterPos + 1;
            END WHILE;
        END LOOP;

        CLOSE cur;
    END ;

Call the procudure.

    CALL SplitAndInsert();

View the result

    SELECT * FROM split_table;

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to select only rows with max value on a column?
2. How to transpose columns to rows in SQL?
3. How to select first row in each GROUP BY group?