How to display rows in columns way

Today I would like to share how to display records in columns way. For those people lazy solve using IF ELSE statement, this is a good way to solve it.

This is the scenario:

tbl_results

student coursecode result
James
PHP
60
Mark
ASP
85
Mark
PHP
83
James
ASP
91


tbl_students

student age
gender
James
20
male
Mark
21
male


We want the output display like this way:

student PHP
ASP
James
60
91
Mark
83
85


This is the SQL statement:

 

SELECT student, php.result as PHP, asp.result as ASP FROM tbl_students
JOIN tbl_results php ON(tbl_students.name = php.student AND php.coursecode='PHP')
JOIN tbl_results asp ON(tbl_students.name = asp.student and asp.courseCode='ASP')


What if in another case, Mark did not take ASP exam, with above code, Mark records would not be display, because of using JOIN (INNER JOIN),
it means both records must be inside the table.
It can be easily solve by using LEFT JOIN tbl_results to solve this issue.

Reference: http://www.omandev.net/2007/08/sqltip2-how-to-display-rows-as-columns.html

Leave a Reply

Your email address will not be published. Required fields are marked *