SQL Split multi value data into multiple rows [duplicate]

Question

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.

Perhaps this helps: stackoverflow.com/questions/17942508/… — Rob Eyre
– Rob Eyre, Commented Jan 10, 2024 at 10:15

SelVazi · Accepted Answer · 2024-01-10 10:11:10Z

1

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

answered Jan 10, 2024 at 10:11

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jcwhall Over a year ago

Great thanks a lot! That's a big help

artoodetoo · Accepted Answer · 2024-01-10 10:25:44Z

1

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.

answered Jan 10, 2024 at 10:25

artoodetoo

93810 silver badges55 bronze badges

Collectives™ on Stack Overflow

SQL Split multi value data into multiple rows [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related