Convert row to column

Question

I have a field called "Shift" kind STRING, storing data in Mysql in this way: '1,2,3,4,5,6,7,8,9'

I need this string line to transform (transpose) into a single column (in this case, it would be the opposite of the GROUP_CONCAT makes), this being a temporary table or not, being like this:

Turno
1
2
3
4
5
6
7
8
9

How to solve?

Answer 1

You need to perform a PIVOT. In other databases, such as SQL Server, it is easier to do this. In Mysql you need to create aggregated expressions, as for example the one I created in Sql Fiddle, take a look around.

Basically, to the data universe below:

CREATE TABLE Turnos (`id` int, `turno` int);

INSERT INTO Turnos (`id`, `turno`)
VALUES
  (1, 1),
  (1, 1),
  (1, 1),
  (1, 2),
  (1, 3),
  (1, 5),
  (1, 5),
  (1, 6),
  (1, 6),
  (1, 7),
  (1, 9),
  (1, 9),
  (1, 9)
;

We make the query as follows:

SELECT 
  SUM(CASE turno WHEN 1 THEN 1 ELSE 0 END) AS '1',
  SUM(CASE turno WHEN 2 THEN 1 ELSE 0 END) AS '2',
  SUM(CASE turno WHEN 3 THEN 1 ELSE 0 END) AS '3',
  SUM(CASE turno WHEN 4 THEN 1 ELSE 0 END) AS '4',
  SUM(CASE turno WHEN 5 THEN 1 ELSE 0 END) AS '5',
  SUM(CASE turno WHEN 6 THEN 1 ELSE 0 END) AS '6',
  SUM(CASE turno WHEN 7 THEN 1 ELSE 0 END) AS '7',
  SUM(CASE turno WHEN 8 THEN 1 ELSE 0 END) AS '8',
  SUM(CASE turno WHEN 9 THEN 1 ELSE 0 END) AS '9'
  FROM Turnos

Reaching the desired result.

Answer 2

After reading your comment in Tiago’s reply, I believe the solution is very similar to yours another question.

Instead of turning an "array" of values into a table to scroll through with a cursor, you can use the Mysql String handling commands to scroll through the items.

Take the example:

CREATE procedure insere_array(array text)
begin
    declare elemento text;
    declare pos int;
    declare pos_ant int;
    set pos = LOCATE(',', array);
    set pos_ant = 1;
    delete from tabela;
    while pos > 0 do

       set elemento = substring(array, pos_ant, pos - pos_ant);
       if length(trim(elemento)) > 0 then
          insert into tabela (item) values (elemento);
       end if;

       set pos_ant = pos + 1;
       set pos = LOCATE(',', array, pos_ant);

    end while;
    if pos_ant <= length(array) then
        set elemento = substring(array, pos_ant, length(array) - pos_ant + 1);
        if length(trim(elemento)) > 0 then
            insert into tabela (item) values (elemento);
        end if;
    end if;
end//

See the Sqlfiddle