How to apply transpose method in MYSQL, Getting rows as collumns have a table of ideas that stores responses from evaluators and stores as one row per evalautor For ex : Evalautor 1 evaluated idea A then in table record gets added as
IDEA A, COMMENTS , EVALUATOR ID

as single idea have 4 evaluator response and i need to show data in this format
IDEA ID ||. EVALUATOR 1 || EVALUATOR 2|| EVALUATOR 3 || EVALUATOR 4
1 --        Comments --    Comments --   Comments --    Comments

Need assistance in creating logic to get information in format given above.
Query i am running :
 SELECT * from Toy_ideas GROUP BY evaluator id

but getting results as
Ideaid -- comments -- evaluator id
As yu will have different evaluators ver the time a dynamic approach is called for
if you have only a static, you can select the variable @sql and get ten result of the dynamc query


CREATE table Toy_ideas  (IDEA int, COMMENTS varchar(50), EVALUATOR_ID int)




INSERT INTO Toy_ideas VALUES(1,'coment1',1),
(1,'coment2',2),
(1,'coment3',3),(1,'coment4',5),
(2,'coment5',2),
(2,'coment6',4),(2,'coment7',5),
(3,'coment8',2),(3,'coment9',5)




SELECT 
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(EVALUATOR_ID = ',
     EVALUATOR_ID,
      ', `COMMENTS`,NULL)) AS `',
      CONCAT('EVALUATOR ',EVALUATOR_ID), '`'      
    )
    ORDER BY EVALUATOR_ID
  ) 
  INTO @sql
FROM Toy_ideas; 
SET @sql = CONCAT('select IDEA,',@sql,' from Toy_ideas
group by `IDEA`');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


IDEA | EVALUATOR 1 | EVALUATOR 2 | EVALUATOR 3 | EVALUATOR 4 | EVALUATOR 5
---: | :---------- | :---------- | :---------- | :---------- | :----------
   1 | coment1     | coment2     | coment3     | null        | coment4    
   2 | null        | coment5     | null        | coment6     | coment7    
   3 | null        | coment8     | null        | null        | coment9    




db&lt;&gt;fiddle here