Pivot a table in MySQL

Question

How to pivot a table in MySQL,

PIvoting table around date column in Mysql

I have a mysql data table as follows

**Student_id    Student_name    Subject Activity    Marks** 

1             Raja          Maths   null              44    
1             Raja          IT  practical     33    
1             Raja          Tamil   null              22    
1             Raja          Histry  null              54    
2             Ganga         Maths   null              33    
2             Ganga         IT  null              22    
2             Ganga         Histry  null              44    
2             Ganga         Tamil   null              89

So i need to pivot my table as below,

Student_id  Student_name    Mths    IT  Tamil   Histry  Activity

1            Raja            44 33  22  54  practical

2            Ganga           33 22  89  44  null

I tried to many ways by using the stackoverflow, but i couldnot able to achieve the target, need a help from the mysql expert,

*"I tried to many ways by using the stackoverflow, but i could not able to achieve the target, "* it is more easy to use `MAX()`/`MIN()` for pivotting as those functions allow to return anny datatype where `SUM()` only works for numeric — Raymond Nijland, Jun 17 '19 at 10:00
I don't understand your required output why are 4 marks shown and only one activity and which activity is chosen? — P.Salmon, Jun 17 '19 at 10:06
Have a look at this article - [Pivot Tables in MySQL](https://codingsight.com/pivot-tables-in-mysql/) — Devart, Dec 03 '19 at 09:23

score 0 · Answer 1 · edited Jul 03 '19 at 10:57

0

I was able to complete the my task by using @Raymond Nijland's suggestion

PIvoting table around date column in Mysql

edited Jul 03 '19 at 10:57

wadie

496
1
10
24

answered Jun 17 '19 at 10:13

kasun rangana

11
2

score 0 · Answer 2 · answered Jun 17 '19 at 10:23

0

select *
from 
(
 select Student_id, Student_name, Subject, Activity, Marks
 from #your_table
 ) src
 pivot
 (
 sum(Marks)
 for Subject in ([Maths], [IT], [Tamil], [Histry])
 ) piv;

Histry is written Mistakenly.. check your table data please.

answered Jun 17 '19 at 10:23

Aditya Dhanraj

189
1
1
12

This will give syntax errors on MySQL as this a SQL Server (MSSQL) query. – Raymond Nijland Jun 17 '19 at 10:41

Pivot a table in MySQL

2 Answers2