CODEWITHSUNDEEP
mysql
Aakeef
Aakeef

Reputation: 29

Transpose row as column in Mysql

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!!!

Upvotes: 0

Views: 891

Answers (1)

Devart
Devart

Reputation: 121912

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.

Upvotes: 1

Related Questions