Transpose row as column in Mysql

Question

While i need to transpose the row into column, I can do that if it just transpose row to column or column to row.

My Table with data.

Student|MONTH|Sub-1|Sub-2|Sub-3
Sunil  |JAN  | 46  |48   |55
Sunil  |APR  |33   |44   |55
Sunil  |JULY |23   |42   |92
Sunil  |OCT  |30   |40   |50
Anil   |JAN  |22   |33   |44
Anil   |JULY |13   |42   |92

and Desired Output.

Student|Sub-1|Sub-2|Sub-3|Sub-1|Sub-2|Sub-3|Sub-1|Sub-2|Sub-3|Sub-1|Sub-2|Sub-3
--------------------------------------------------------------------------------------
Stu1   |6    |48   |55   |33   |44   |55   |23   |42   |92   |30   |40   |50
Stu2   |22   |33   |44   |     |     |     |13   |42   |92   |98

etc..

Anybody know how to do this? Thanks very much!!!

Possible duplicate : http://stackoverflow.com/questions/16568228/how-to-transpose-mysql-table-rows-into-columns — Joke_Sense10, Dec 13 '13 at 07:33

score 1 · Accepted Answer · answered Dec 13 '13 at 08:08

1

You can use this solution (pivot table) -

SELECT
  Student,
  MAX(IF(MONTH = 'JAN', `Sub-1`, NULL)) `JAN-Sub-1`,
  MAX(IF(MONTH = 'JAN', `Sub-2`, NULL)) `JAN-Sub-2`,
  MAX(IF(MONTH = 'JAN', `Sub-3`, NULL)) `JAN-Sub-3`,
  MAX(IF(MONTH = 'FEB', `Sub-1`, NULL)) `FEB-Sub-1`,
  MAX(IF(MONTH = 'FEB', `Sub-2`, NULL)) `FEB-Sub-2`,
  MAX(IF(MONTH = 'FEB', `Sub-3`, NULL)) `FEB-Sub-3`
FROM
  trans
GROUP BY
  student

...add columns for other months.

answered Dec 13 '13 at 08:08

Devart

119,203
23
166
186

Its a nice solution and it helped me. Thank you. Can you help out to solve this issue when I have dynamic data? Like tomorrow I may get FEB-Sub-4 and later I may OCT-Sub-6.... – Pawan Oct 18 '18 at 07:26

Transpose row as column in Mysql

1 Answers1