Column for Mysql row

Question

I have the following table:

    |    Roll No   | Student Name |   Q1   |  Q2  |  Q3  |
    |--------------|--------------|--------|------|------|
    |            1 |      Aluno 1 |      1 |    1 |    1 |
    |            2 |      Aluno 2 |      1 |    1 |    0 |
    |            3 |      Aluno 3 |      1 |    1 |    1 |

I would like a consultation that brings me the following result, I tried but I could not:

| Roll No | Student Name | Nota | idquestion |
|---------|--------------|------|------------|
|       1 |      Aluno 1 |    1 |     Q1     |
|       1 |      Aluno 1 |    1 |     Q2     |
|       1 |      Aluno 1 |    1 |     Q3     |

|       2 |      Aluno 2 |    1 |     Q1     |
|       2 |      Aluno 2 |    1 |     Q2     |
|       2 |      Aluno 2 |    0 |     Q3     |

|       3 |      Aluno 3 |    1 |     Q1     |
|       3 |      Aluno 3 |    1 |     Q2     |
|       3 |      Aluno 3 |    1 |     Q3     |

Example in sqlfiddle
http://sqlfiddle.com/#! 9/fe0f85/1/0

Answer 1

An alternative is to divide the query into three subselects, one for each idquestion; the "problem" is that there will be several queries, in case the bank is too big, can get heavy.

select `Roll No`, `Student Name`, `Nota`, `idquestion` from (
  select `Roll No`, `Student Name`, `Q. 1` as `Nota`, 'Q. 1' as `idquestion` from `TABLE`
  union all
  select `Roll No`, `Student Name`, `Q. 2` as `Nota`, 'Q. 2' as `idquestion` from `TABLE`
  union all
  select `Roll No`, `Student Name`, `Q. 3` as `Nota`, 'Q. 3' as `idquestion` from `TABLE`) as tabela
order by `Roll No`, `idquestion`

detail: if you have more notes, it would be(m) necessary(s) more subselect(s). Example in fiddle

---

edited - generic form

I decided to go for a more generic code. I followed the following logic:

1. I created temporary tables to store column names;
2. for each of the columns, will be consulted and inserted in another temporary table the data of each user referring to the note of the specific proof;
3. Finally, query the data table, sorting by user and step.

Below I put the code but I find it easier to understand/exemplify in this fiddle. I believe that this way you can make the consultation independent of the number of columns, without needing the thousand Unions.

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.%'; -- aqui segui a lógica do exemplo no seu fiddle

CREATE TABLE `table_tmp` (
  `Roll No` int(10),
  `Student Name` varchar(39),
  `Nota` int(1),
  `idquestion` varchar(10));
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`;