mysql split row to multiple columns and rows

Question

I have a table with the following structure and these values in column assoc.

| id | assoc |

| 1 | |3|-1|107|-4|146|-6| |

| 2 | |19|-3|107|-5| |

| 3 | |42|-1| |

You can see it here

This is a wrong mysql table structure. So I think that the right structure it must be:

| id | assoc | attrib | order |

| 1  | 3 | 1 | 1 |

| 1  | 107 | 4 | 2 |

| 1  | 146 | 6 | 3 |

| 2  | 19 | 3 | 1 |

| 2  | 107 | 5 | 2 |

| 3  | 42 | 1 | 1 |

Is possible to do with a mysql script on phpmyadmin?

“How it can be converted?” – for example by a script that you write for that purpose … — C3roe
– C3roe, Commented Mar 23, 2014 at 17:18

Mihai · Accepted Answer · 2014-03-23 20:26:11Z

1

SET @prev := null;

SET @cnt := 0;

SELECT id,blah,mah,IF(@prev <> id, @cnt := 1, @cnt := @cnt + 1) AS rank, @prev := id 
FROM(
SELECT id,REPLACE(SUBSTRING_INDEX(assoc,'|',2),'|','')*1 as blah, 
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(assoc,'-0'),'|',3),'-',-1)as mah FROM table1
UNION ALL
SELECT id,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(assoc,'|0'),'|',4),'|',-1)*1 as blah,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(assoc,'-0'),'|',5),'-',-1)as mah FROM table1
UNION ALL
SELECT id,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(assoc,'|0'),'|',6),'|',-1)*1 as blah,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(assoc,'-0'),'|',7),'-',-1)as mah FROM table1
)x
WHERE   x.mah !='0'
ORDER BY x.id ,x.blah

FIDDLE

answered Mar 23, 2014 at 20:26

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

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

1 Comment

Mihai Over a year ago

@user3450939 it would be nice if you accepted the answer(click under the score arrows)

Collectives™ on Stack Overflow

mysql split row to multiple columns and rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related