1

I have three table (student's_info,subject and marks):

this is table student's_info:

----------------
|s_id | s_name |
----------------
| 1 | Ali      |
| 2 | Jon      |
---------------

This is table subjects:

 ----------------
|sub_id | subject|
----------------
| 1   | Word     |
| 2   | Windows  |
| 3   | Excel    |
-----------------

and this is table marks:

 -----------------------
|id | s_id |sub_id|n_marks|
------------------------
| 1 |  1   |  1   |  50 |
| 2 |  1   |  2   |  44 | 
| 3 |  1   |  3   |  90 |
| 4 |  2   |  1   |  32 |
| 5 |  2   |  2   |  56 |
| 6 |  2   |  3   |  66 |
------------------------

i used this code to transpose rows as column:

SELECT 
students_info.*,marks.*,
CASE WHEN subject = 'Word' THEN marks.n_mark ELSE 0 END AS `Word`,
CASE WHEN subject = 'Windows' THEN marks.n_mark ELSE 0 END AS `Windows`,
CASE WHEN subject = 'Excel' THEN marks.n_mark ELSE 0 END AS `Excel`

FROM students_info
INNER JOIN marks ON students_info.s_id = marks.s_id
INNER JOIN subjects ON marks.sub_id = subjects.sub_id
GROUP BY students_info.s_name;

This is my question: I wan to the name subjects in the table subject appear at the header of my table, i don't write name subject by wizard with this{CASE WHEN subject = 'Word'}...

I do not want to write subject tables in my codes for myself , because I have the name subjects in the subject tables ,that is why i need a code to select name subjects.

see this image to understand my question:

enter image description here

Please review my code and give me a help, to do work this code.

Thank you in advance.

  • Thanks for your answer @Sam, I do not want to write subject tables in my codes for myself , because I have the name subjects in the subject tables ,that is why i need a code to select name subjects. – Talib Jun 24 '18 at 6:59
  • See the tag I added. – Rick James Jun 24 '18 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.