Column for MySql row

Question

Column for MySql row

Navigation

#1 by (4 votes)

3

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 query that would bring me the following result, I tried but 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
link

mysql sql

asked by anonymous 07.10.2018 / 01:12

1 answer

Problem with sum in vector in C Sort a list of objects by more than one attribute

score 4 · Accepted Answer

An alternative is to split the query into three subselects , one for each idquestion ; the "problem" is that there will be several queries, if the database is too large, it 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, you would need more subselect (s) . Example in fiddle

edited - generic form

I decided to go after a more general code. I followed the following logic:

I created temporary tables to store the column names;

For each of the columns, the data of each user referring to the specific test note will be queried and inserted in another temporary table;

Finally, query the data table, sorting by user and step.

I downloaded the code below but I find it easier to understand / exemplify in this fiddle . I believe that this way you can do the independent query 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';