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

Mysql – Put comma seperated data from one table into a new table as seperate rows

I’m trying to get a comma separated list of values from one table into a new table as separate rows. Without losing key-value relations.

Table 1 (original data)

key values
key1 val1,val2,val3,val4
key2 val1,val2,val3,val4

Table 2 (new data)

key value
key1 val1
key1 val2
key1 val3
key1 val4
key2 val1
key2 val2
etc…

I have no idea how to do this in MYSQL. Not a clue where to start or how to even Google it.

Tags:

2

Answers


  1. 0

    Try using string splitting functions along with a cross apply.
    Like this.

    INSERT INTO Table2 ([key], value) SELECT t1.[key], s.value FROM Table1 t1 CROSS APPLY STRING_SPLIT(t1.[values], ',') s;

    Login or Signup to reply.
  2. 0

    Here a sample for you. You can also found this by google

    create a table like this

    CREATE TABLE `tagstrings` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `mykey` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `mytags` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

    fill it

    INSERT INTO `tagstrings` (`id`, `mykey`, `mytags`)
VALUES
    (1, 'key1', 'Luke,Bernd,Paul'),
    (2, 'key2', 'Ann,Lisa'),
    (3, 'key3', 'Joe'),
    (4, 'key4', 'Robert,David,Axel,Frank');

    SELECT it

    SELECT * tagstrings;

1   key1    Luke,Bernd,Paul
2   key2    Ann,Lisa
3   key3    Joe
4   key4    Robert,David,Axel,Frank

    query

    SELECT t.mykey, tags.tag FROM tagstrings t
CROSS JOIN JSON_TABLE(CONCAT('["', REGEXP_REPLACE(mytags, ', *', '","'), '"]'),
  '$[*]' COLUMNS(tag VARCHAR(20) PATH '$')
) AS tags;

    result

    key1    Luke
key1    Bernd
key1    Paul
key2    Ann
key2    Lisa
key3    Joe
key4    Robert
key4    David
key4    Axel
key4    Frank
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search