How to Split Combined Data Values with Comma into Separate Rows in MySQL

When data values are stored in a single column and combined with commas, you can split these values into individual rows using MySQL. This process involves creating a table view to handle the splitting. Below is an example demonstrating how to achieve this in MySQL.

Steps to Split Comma-Separated Values in MySQL

1. Create a Table

First, you need to create a table that will hold your data, including a column with comma-separated values.

CREATE TABLE tablename (
    ProductID INT,
    ProductName VARCHAR(255),
    LocationNames VARCHAR(255),
    Country VARCHAR(100),
    ProductSalesCount INT,
    TotalSales DECIMAL(10, 2)
);

2. Insert Sample Data

Populate your table with sample data, making sure at least one of the columns contains comma-separated values.

INSERT INTO tablename (ProductID, ProductName, LocationNames, Country, ProductSalesCount, TotalSales)
VALUES
   (1, 'Green Tea', 'Mumbai, Delhi, Bengaluru', 'India', 400, 1600.00),
   (2, 'Bottled Water', 'New York, Los Angeles, Chicago', 'USA', 1200, 3600.00),
   (3, 'Chocolate Bars', 'London, Manchester, Birmingham', 'UK', 800, 2400.00),
   (4, 'Canned Soda', 'Sydney, Melbourne, Brisbane', 'Australia', 1500, 4500.00),
   (5, 'Sugar', 'Rome, Milan, Florence', 'Italy', 600, 1800.00); 

3. Create a View to Split the Data

Next, create a view that will handle the splitting of the LocationNames column into separate rows using the JSON_TABLE function. This function allows you to convert the comma-separated string into JSON format, which can then be processed to produce individual rows.

CREATE OR REPLACE VIEW Split_values AS
SELECT 
   ps.ProductID,
   ps.ProductName,
   TRIM(Split.SplitLocation) AS LocationNames,
   ps.Country,
   ps.ProductSalesCount,
   ps.TotalSales
FROM tablename ps,
 JSON_TABLE(
   CONCAT('["', REPLACE(ps.LocationNames, ',', '","'), '"]'),
   '$[*]' COLUMNS (SplitLocation VARCHAR(255) PATH '$')
 ) AS Split; 

4. View the Split Data

Finally, you can query the view to see the results, which will show each location in a separate row.

SELECT * FROM Split_values;

Result

The query will yield results similar to the following: