I have a field called "Shift" of type STRING , storing data in MySql like this: '1,2,3,4,5,6,7,8,9'
I need this string to transpose (transpose) into a single column (in this case, it would be the inverse of what GROUP_CONCAT does), this being a temporary table or not, thus:
Turno
1
2
3
4
5
6
7
8
9
How to solve?
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 aggregate expressions, such as the one I created in Sql Fiddle, , take a look around there.
Basically, for 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 made 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.
After reading your comment on Tiago's answer, I think the solution is very similar to your other question .
Instead of transforming an array of values into a table to traverse with a cursor, you can use the MySQL String handling commands to move through the items.
See 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//