Many of us struggle with how to display mysql rows as columns. For example, having student details and subject and mark in a table. we need to show student, and is each subject in a column. here we going to discuss how to Display rows as columns in table using php and mysql
Step 1:
We have a table with details like the image bellow.
or here is the code for table
CREATE TABLE IF NOT EXISTS `exam` ( `id` int(11) NOT NULL AUTO_INCREMENT, `student` varchar(55) DEFAULT NULL, `subject` varchar(255) DEFAULT NULL, `mark` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Step 2 :
Here is the mysql code for select row as column
SELECT Student, MAX(CASE WHEN subject = 'English' THEN mark ELSE NULL END) `English`, MAX(CASE WHEN subject = 'Maths' THEN mark ELSE NULL END) `Maths`, MAX(CASE WHEN subject = 'Physics' THEN mark ELSE NULL END) `Physics`, MAX(CASE WHEN subject = 'Chemistry' THEN mark ELSE NULL END) `Chemistry` FROM exam GROUP BY Student
So above code is simply set ‘Subject’ column as each subject like ‘english,maths..’ compare with ‘mark’ using “MAX” keyword and print maximum to minimum values as each column.
Output look like bellow image
That’s it. enjoy code