How to split values to multiple rows in SQL?
Let's see how to split values to multiple rows in SQL with an example problem and working solution.
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;