How to arrange data from one row into several columns?

Question

MySQL

query in phpmyadmin we want to display student grades in several columns like this:
[student name] [subject] [grade A] [grade B] [grade C]
grades are stored in the line prefix_grade_grades.finalgrade and each has a unique identifier, which is stored in prefix_grade_grades.idnumber

Answer 1

If it means that several grades are stored in the prefix_grade_grades.finalgrade line at once, then they can be pulled out using a combination of the
substr and position
functions . If each grade is stored in its own line, then what's the problem? Normal join (for a more detailed answer, provide more details about your tables)

Answer 2

select
t.'Display Name',
t.'Course',
t.'Category',
t.'Item Name',
MAX(CASE WHEN t.'idnumber' = 'a1' THEN t.'Grade' ELSE NULL END) AS 'оценка А',
MAX(CASE WHEN t.'idnumber' = 'b1' THEN t.'Grade' ELSE NULL END) AS 'оценка Б'
// и так далее:  c1,d1 и t1
from
( 
// ВАШ ЗАПРОС, добавил - gg.idnumber AS 'idnumber'
  SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS            'Display Name',
  c.fullname AS 'Course', 
  cc.name AS 'Category',

  CASE 
  WHEN gi.itemtype = 'course' 
  THEN c.fullname + ' Course Total'
  ELSE gi.itemname
  END AS 'Item Name',

  ROUND(gg.finalgrade,2) AS Grade,

  gg.idnumber AS 'idnumber'

  FROM prefix_course AS c
  JOIN prefix_context AS ctx ON c.id = ctx.instanceid
  JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
  JOIN prefix_user AS u ON u.id = ra.userid
  JOIN prefix_grade_grades AS gg ON gg.userid = u.id
  JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
  JOIN prefix_course_categories AS cc ON cc.id = c.category

  WHERE gi.courseid = c.id 
  ORDER BY lastname
) t
group by t.'Display Name',
t.'Course',
t.'Category',
t.'Item Name'

Answer 3

Excel has a built-in "Text by Columns" function.
It can be loaded from the text into Excel, processed there and transferred to the database

Answer 4

https://technet.microsoft.com/en-us/library/ms1774...