teste com N colunas, MySQL

Language: Layout:

DROP TABLE IF EXISTS `table`, `tabela_colunas`, `table_tmp`;
DROP PROCEDURE IF EXISTS ConsultarNotas;

CREATE TABLE `TABLE` (
  `Roll No` int(10) UNSIGNED NOT NULL,
  `Student Name` varchar(39) DEFAULT NULL,
  `Total Marks` int(2) DEFAULT NULL,
  `Rank` int(2) DEFAULT NULL,
  `Subject 1` decimal(3,1) DEFAULT NULL,
  `Q. 1` int(1) DEFAULT NULL,
  `Q. 2` int(1) DEFAULT NULL,
  `Q. 3` int(1) DEFAULT NULL,
  `Q. 4` int(1) DEFAULT NULL,
  `Q. 5` int(1) DEFAULT NULL,
  `Q. 6` int(1) DEFAULT NULL,
  `Q. 7` int(1) DEFAULT NULL,
  `Q. 8` int(1) DEFAULT NULL,
  `Q. 9` int(1) DEFAULT NULL,
  `Q. 10` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `TABLE` (`Roll No`, `Student Name`, `Total Marks`, `Rank`, `Subject 1`, `Q. 1`, `Q. 2`, `Q. 3`, `Q. 4`, `Q. 5`, `Q. 6`, `Q. 7`, `Q. 8`, `Q. 9`, `Q. 10`) VALUES
(1, 'Aluno 1', 10, 1, '10.0', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1),
(2, 'Aluno 2', 8, 38, '8.0', 1, 1, 0, 1, 1, 1, 1, 1, 0, 1),
(3, 'Aluno 3', 9, 20, '9.0', 1, 1, 1, 1, 1, 1, 1, 1, 1, 0),
(10, 'Aluno 4', 9, 20, '9.0', 1, 1, 1, 1, 1, 0, 1, 1, 1, 1);

create table `tabela_colunas` (`id` int, `coluna` varchar(100));

SET @row_number := 0;
INSERT INTO tabela_colunas (id, coluna)
SELECT (@row_number:=@row_number + 1) AS id, column_name as 'coluna' FROM information_schema.columns
WHERE table_name = 'TABLE' AND column_name like 'Q.%';

CREATE TABLE `table_tmp` (
  `Roll No` int(10),
  `Student Name` varchar(39),
  `Nota` int(1),
  `idquestion` varchar(10));

CREATE PROCEDURE ConsultarNotas()
BEGIN
    SET @id_tabela:=1, @total:=(SELECT COUNT(*) FROM tabela_colunas);

WHILE @id_tabela <= @total DO
        set @sql := concat(
           'insert into table_tmp (`Roll No`, `Student Name`, `Nota`, `idquestion`) select `Roll No`, `Student Name`, `',
           (SELECT coluna FROM tabela_colunas WHERE id = @id_tabela), '` as Nota, \'',
           (SELECT coluna FROM tabela_colunas WHERE id = @id_tabela), '\' as idquestion', ' from `table`');

PREPARE myquery FROM @sql;
        EXECUTE myquery;

SET @id_tabela = @id_tabela + 1;    
    END WHILE;
    
    SELECT * FROM table_tmp ORDER BY `Roll No`, `idquestion`;
END;

call ConsultarNotas();


absolute service time: 3,27 sec