How to transposing rows into columns in MySQL?

Question

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;

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.

What you need is dynamic SQL. See https://stackoverflow.com/a/26297463/2095534 — Kamil Gosciminski, Jun 24 '18 at 07:26
Seems to me like it is exactly what you need adjusted to your data. — Kamil Gosciminski, Jun 24 '18 at 07:51
@KamilG. I need the subject names in table subject make table header. — Talib, Jun 24 '18 at 08:05
And I would still say that this has been answered on SO and you only need one more JOIN to do so :-) — Kamil Gosciminski, Jun 24 '18 at 08:06
bro see this image to understand my question (https://ibb.co/b6ZbN8). — Talib, Jun 24 '18 at 08:12
I want a code to appear name subjects in header of my tables but don't use a CASE WHEN. — Talib, Jun 24 '18 at 08:15
@Talib you have to use if() or case when. To make it dynamic, you need dynamic sql as demonstrated by the duplicate topic. — Shadow, Jun 24 '18 at 08:16
Why not use CASE WHEN statement? You can build it **dynamically** so that you don't have to handle every case by hand! — Kamil Gosciminski, Jun 24 '18 at 08:16
@KamilG. Because we must change a code again to add new subject, i want to a code no changed while adding an other subjects to table subjects. — Talib, Jun 24 '18 at 08:31
That's what the duplicate topic does - you can find the link at the top of this page. Pls check it out. — Shadow, Jun 24 '18 at 09:27

How to transposing rows into columns in MySQL?

0 Answers0