Transpose duplicated rows to columns in mysql

Question

I have 2 tables, student table that contains regNo and name columns; and reg_subject table which contains list of subjects registered individually by students. A student can register more than one subject.

I want to generate a table that will join the two tables such that the reg_subject table will be transposed in a way that the rows will turn to be the columns. The two tables as well as the resultant table are sketched below.

student table

| regNo | name  |
_________________
| 1001  | Saleh |
-----------------
| 1002  | Moses |
-----------------
| 1003  | Hafsa |

reg_subject table

| regNo | subject  |
____________________
| 1001  | English  |
--------------------
| 1001  | Math     |
--------------------
| 1002  | English  |
--------------------
| 1003  | Math     |
--------------------
| 1002  | Math     |
--------------------
| 1003  | Physics  |
--------------------
| 1001  | Physics  |
--------------------
| 1002  | Physics  |
--------------------

the transposed join table should look like this:

The transposed joint table

| regNo | name  | subject1 | subject2 | subject3 |
__________________________________________________
| 1001  | Saleh | English  | Math     | Physics  |
--------------------------------------------------
| 1002  | Moses | English  | Math     | Physics  |
--------------------------------------------------
| 1003  | Hafsa |          | Math     | Physics  |
--------------------------------------------------

Why create a table? This could be done when you need it in a query. Also, is there a maximum number of subjects per regNo? If you are creating the table, how would you maintain it as the source data changes? Would you empty the table and re-create it? — Sloan Thrasher, Sep 03 '18 at 14:56
You should definitely assemble your output table *outside your SQL-code*. A PHP (back end) or JavaScript (front end) script can do this in a much more elegant fashion. — Carsten Massmann, Sep 03 '18 at 15:04
So what is the problem? Looks like you know about pivot, there are lot of examples about that. https://stackoverflow.com/questions/7674786/mysql-pivot-table — Juan Carlos Oropeza, Sep 03 '18 at 15:05
Thank you all for your concerns. I only know little about Transposing table. The third table is what I want to generate from the two tables above The maximum number of subjects to register is 3. only that I omitted 1 subject for Hafsat in case she did not register the third subject — saleh zaharaddeen, Sep 03 '18 at 15:37

score 0 · Answer 1 · answered Sep 03 '18 at 16:48

 SELECT regNo, name,               
        MAX( CASE WHEN subject = 'English'
                  THEN subject
             END) as subject1,
        MAX( CASE WHEN subject = 'Math'
                  THEN subject
             END) as subject2,
        MAX( CASE WHEN subject = 'Physics'
                  THEN subject
             END) as subject3
FROM YourTable
GROUP BY regNo, name

Transpose duplicated rows to columns in mysql

1 Answers1