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;