By turning the rows of the table into columns in mysql
source link: https://www.codesd.com/item/by-turning-the-rows-of-the-table-into-columns-in-mysql.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
By turning the rows of the table into columns in mysql
Say I have a database similar to the following:
Table student_info:
id name
111 jon
112 dan
113 david
...
and table scores:
item_id student_id score
01 111 37
02 111 45
01 112 55
02 112 44
01 113 66
02 113 45
...
Is it possible to do a mysql query to generate the following table?:
Student_Name ITEM_1_SCORE ITEM_2_SCORE
jon 37 45
dan 55 44
david 66 45
...
If so, what would the syntax be? I don't know if this is a join operation or something else?
thanks.
If you have only two values for item_id
, then it is fine to hard code values. Example
SELECT a.Name AS Student_Name,
MAX(CASE WHEN item_id = '01' THEN b.score END) Item_1_Score,
MAX(CASE WHEN item_id = '02' THEN b.score END) Item_2_Score
FROM student_info a
LEFT JOIN scores b
ON a.id = b.student_ID
GROUP BY a.Name
Otherwise, when you have unknow number of scores, a Dynamic SQL
is much prefered.
SELECT GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE WHEN item_id = ''',
item_id,
''' THEN Score END) AS ',
CONCAT('`Item_', item_id, '_Score`')
)) INTO @sql
FROM scores;
SET @sql = CONCAT('SELECT a.Name AS Student_Name, ', @sql, '
FROM student_info a
LEFT JOIN scores b
ON a.id = b.student_ID
GROUP BY a.Name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Both queries will OUTPUT the same
╔══════════════╦══════════════╦══════════════╗
║ STUDENT_NAME ║ ITEM_1_SCORE ║ ITEM_2_SCORE ║
╠══════════════╬══════════════╬══════════════╣
║ dan ║ 55 ║ 44 ║
║ david ║ 66 ║ 45 ║
║ jon ║ 37 ║ 45 ║
╚══════════════╩══════════════╩══════════════╝
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK