Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Split multi value data into multiple rows

I have a SQL table with the following data:

| Name     | Colour             | Shape            | 
| -------- | ------------------ | ---------------- |
| Bob      | Blue, Green        | Circle, Square   |
| Phil     | Yellow             |                  |
| Steve    | Green, Orange, Red | Square, Triangle |

I want to create a view/table of it which looks like:

| Name     | Colour | Shape    | 
| -------- | -------| ---------|
| Bob      | Blue   | Circle   |
| Bob      | Blue   | Square   |
| Bob      | Green  | Circle   |
| Bob      | Green  | Square   |
| Phil     | Yellow |          |
| Steve    | Green  | Square   |
| Steve    | Green  | Triangle |
| Steve    | Orange | Square   |
| Steve    | Orange | Triangle |
| Steve    | Red    | Square   |
| Steve    | Red    | Triangle |

Does anyone know how to do this?

I’ve tried to split the multiple value data into multiple rows using a distinct union, but it’s failing to generate the correct result.

Tags:

2

Answers


  1. 0

    You need to convert the comma-separated values into JSON format and then use the JSON_TABLE() function to separate the data in the Colour and Shape columns into multiple rows.

    select t.Name, c.Colour, s.Shape
from mytable t
join json_table(
  replace(json_array(t.Colour), ',', '","'),
  '$[*]' columns (Colour varchar(50) path '$')
) c
join json_table(
  replace(json_array(t.Shape), ',', '","'),
  '$[*]' columns (Shape varchar(50) path '$')
) s;

    Demo here

    Login or Signup to reply.
  2. 0

    I made the assumption that you have “dictionaries” for color and shape. In this case, you have data to join with a comma-separated list.

    SELECT DISTINCT r.name, c.colour, s.shape
FROM rawdata AS r
LEFT JOIN colours AS c ON FIND_IN_SET(c.colour, r.colour)
LEFT JOIN shapes AS s ON FIND_IN_SET(s.shape, r.shape)
ORDER BY r.name ASC, c.colour ASC, s.shape ASC

    Demo here

    P.S. I didn’t care about spaces in CSV. This could make the query more verbose. I left only what expresses the essence. Sorry.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search