SQL split values to multiple rows

Question

I have table    id   name     1    a b c     2    b   i want output like this    id   name     1    a     1    b     1    c     2    b

User · Answer

The original question was for MySQL and SQL in general. The example below is for the new versions of MySQL. Unfortunately, a generic query that would work on any SQL server is not possible. Some servers do no support CTE, others do not have substring_index, yet others have built-in functions for splitting a string into multiple rows.

--- the answer follows ---

Recursive queries are convenient when the server does not provide built-in functionality. They can also be the bottleneck.

The following query was written and tested on MySQL version 8.0.16. It will not work on version 5.7-. The old versions do not support Common Table Expression (CTE) and thus recursive queries.

with recursive
  input as (
        select 1 as id, 'a,b,c' as names
      union
        select 2, 'b'
    ),
  recurs as (
        select id, 1 as pos, names as remain, substring_index( names, ',', 1 ) as name
          from input
      union all
        select id, pos + 1, substring( remain, char_length( name ) + 2 ),
            substring_index( substring( remain, char_length( name ) + 2 ), ',', 1 )
          from recurs
          where char_length( remain ) > char_length( name )
    )
select id, name
  from recurs
  order by id, pos;

User · Answer

My variant  stored procedure that takes table name  field names and delimiter as arguments  Inspired by post http   www marcogoncalves com 2011 03 mysql-split-column-string-into-rows    delimiter     DROP PROCEDURE IF EXISTS split value into multiple rows    CREATE PROCEDURE split value into multiple rows tablename VARCHAR 20       id column VARCHAR 20   value column VARCHAR 20   delim CHAR 1     BEGIN     DECLARE id INT DEFAULT 0      DECLARE value VARCHAR 255       DECLARE occurrences INT DEFAULT 0      DECLARE i INT DEFAULT 0      DECLARE splitted value VARCHAR 255       DECLARE done INT DEFAULT 0      DECLARE cur CURSOR FOR SELECT tmp table1 id  tmp table1 value FROM          tmp table1 WHERE tmp table1 value IS NOT NULL AND tmp table1 value            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done   1       SET  expr   CONCAT  CREATE TEMPORARY TABLE tmp table1  id INT NOT NULL  value VARCHAR 255   ENGINE Memory SELECT            id column   id     value column   value FROM   tablename       PREPARE stmt FROM  expr      EXECUTE stmt      DEALLOCATE PREPARE stmt       DROP TEMPORARY TABLE IF EXISTS tmp table2      CREATE TEMPORARY TABLE tmp table2  id INT NOT NULL  value VARCHAR 255  NOT NULL  ENGINE Memory       OPEN cur        read loop  LOOP         FETCH cur INTO id  value          IF done THEN           LEAVE read loop          END IF           SET occurrences    SELECT CHAR LENGTH value  -                            CHAR LENGTH REPLACE value  delim         1           SET i 1          WHILE i  lt   occurrences DO           SET splitted value    SELECT TRIM SUBSTRING INDEX                SUBSTRING INDEX value  delim  i   delim  -1               INSERT INTO tmp table2 VALUES  id  splitted value             SET i   i   1          END WHILE        END LOOP         SELECT   FROM tmp table2      CLOSE cur      DROP TEMPORARY TABLE tmp table1    END      delimiter     Usage example  normalization    CALL split value into multiple rows  my contacts    contact id    interests          CREATE TABLE interests     interest id INT NOT NULL AUTO INCREMENT PRIMARY KEY    interest VARCHAR 30  NOT NULL   SELECT DISTINCT value interest FROM tmp table2   CREATE TABLE contact interest     contact id INT NOT NULL    interest id INT NOT NULL    CONSTRAINT fk contact interest my contacts contact id FOREIGN KEY  contact id  REFERENCES my contacts  contact id     CONSTRAINT fk contact interest interests interest id FOREIGN KEY  interest id  REFERENCES interests  interest id    SELECT my contacts contact id  interests interest id     FROM my contacts  tmp table2  interests     WHERE my contacts contact id   tmp table2 id AND interests interest   tmp table2 value

User · Answer

I have take the reference from here with changed column name   DELIMITER     CREATE FUNCTION strSplit x VARCHAR 65000   delim VARCHAR 12   pos INTEGER   RETURNS VARCHAR 65000  BEGIN   DECLARE output VARCHAR 65000     SET output   REPLACE SUBSTRING SUBSTRING INDEX x  delim  pos                     LENGTH SUBSTRING INDEX x  delim  pos - 1     1                     delim                           IF output      THEN SET output   null  END IF    RETURN output  END      CREATE PROCEDURE BadTableToGoodTable   BEGIN   DECLARE i INTEGER     SET i   1    REPEAT     INSERT INTO GoodTable  id  name        SELECT id  strSplit name       i  FROM BadTable       WHERE strSplit name       i  IS NOT NULL      SET i   i   1      UNTIL ROW COUNT     0   END REPEAT  END     DELIMITER

User · Answer

Here is my attempt  The first select presents the csv field to the split  Using recursive CTE  we can create a list of numbers that are limited to the number of terms in the csv field  The number of terms is just the difference in the length of the csv field and itself with all the delimiters removed  Then joining with this numbers  substring index extracts that term   with recursive     T as   select  a b c d e f  as items       N as   select 1 as n union select n   1 from N  T         where n  lt   length items  - length replace items                 select distinct substring index substring index items       n        -1  group name from N  T

User · Answer

Best Practice  Result  SELECT SUBSTRING INDEX SUBSTRING INDEX  ab bc cd      help id 1      -1  AS oid FROM   SELECT  xi   xi 1 as help id from   SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5  xc1   SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5  xc2   SELECT  xi  -1  xc0   a WHERE  help id  lt  LENGTH  ab bc cd  -LENGTH REPLACE  ab bc cd           1  First  create a numbers table  SELECT  xi   xi 1 as help id from   SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5  xc1   SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5  xc2   SELECT  xi  -1  xc0     help id      ---     0       1       2       3                 24      Second  just split the str  SELECT SUBSTRING INDEX SUBSTRING INDEX  ab bc cd      help id 1      -1  AS oid FROM numbers table WHERE help id  lt  LENGTH  ab bc cd  -LENGTH REPLACE  ab bc cd           1    oid      ---     ab       bc       cd

User · Answer

CREATE PROCEDURE  getVal    BEGIN         declare r len integer          declare r id integer          declare r val varchar 20           declare i integer          DECLARE found row int 10           DECLARE row CURSOR FOR select length replace val          id val from split          create table x id int name varchar 20          open row              select FOUND ROWS   into found row               read loop  LOOP                 IF found row   0 THEN                          LEAVE read loop                  END IF              set i   1                FETCH row INTO r len r id r val              label1  LOOP                         IF i  lt   r len THEN                   insert into x values  r id SUBSTRING replace r val         i 1                      SET i   i   1                    ITERATE label1                  END IF                  LEAVE label1              END LOOP label1              set found row   found row - 1              END LOOP          close row          select   from x          drop table x  END

User · Answer

Here is my solution  -- Create the maximum number of words we want to pick  indexes in n  with recursive n i  as       select         1 i     union all     select i 1 from n where i  lt  1000   select distinct     s id      s oaddress      -- n i      -- use the index to pick the nth word  the last words will always repeat  Remove the duplicates with distinct     if instr reverse trim substring index s oaddress     n i          gt  0          reverse substr reverse trim substring index s oaddress     n i    1              instr reverse trim substring index s oaddress     n i                    trim substring index s oaddress     n i    oth from      app schools s      n

User · Answer

If you can create a numbers table  that contains numbers from 1 to the maximum fields to split  you could use a solution like this   select   tablename id    SUBSTRING INDEX SUBSTRING INDEX tablename name       numbers n        -1  name from   numbers inner join tablename   on CHAR LENGTH tablename name       -CHAR LENGTH REPLACE tablename name            gt  numbers n-1 order by   id  n   Please see fiddle here   If you cannot create a table  then a solution can be this   select   tablename id    SUBSTRING INDEX SUBSTRING INDEX tablename name       numbers n        -1  name from    select 1 n union all    select 2 union all select 3 union all    select 4 union all select 5  numbers INNER JOIN tablename   on CHAR LENGTH tablename name       -CHAR LENGTH REPLACE tablename name            gt  numbers n-1 order by   id  n   an example fiddle is here

User · Answer

If the name column were a JSON array  like    a   b   c      then you could extract unpack it with JSON TABLE    available since MySQL 8 0 4    select t id  j name from mytable t join json table    t name           columns  name varchar 50  path        j    Result     id    name     ---   ----     1     a        1     b        1     c        2     b        View on DB Fiddle  If you store the values in a simple CSV format  then you would first need to convert it to JSON   select t id  j name from mytable t join json table    replace json array t name                         columns  name varchar 50  path        j   Result     id    name     ---   ----     1     a        1     b        1     c        2     b        View on DB Fiddle

[mysql] SQL split values to multiple rows

Examples related to mysql

Examples related to sql

Examples related to delimiter

Examples related to delimiter-separated-values