We help IT Professionals succeed at work.
Get Started

MYSQL Pivot Table

LimMH
LimMH asked
on
1,033 Views
Last Modified: 2021-04-21
I will like to make a pivot table out of records from the table.

My table contains the following data:
CREATE TABLE IF NOT EXISTS `test` (
  `Name` varchar(20) NOT NULL,
  `Test` varchar(5) NOT NULL,
  `Marks` varchar(3) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`Name`, `Test`, `Marks`) VALUES
('StudentA', 'Test1', '50'),
('StudentA', 'Test2', '65'),
('StudentA', 'Test3', '77'),
('StudentB', 'Test1', '45'),
('StudentB', 'Test2', '68'),
('StudentB', 'Test3', '73'),
('StudentC', 'Test1', '59'),
('StudentC', 'Test2', '64'),
('StudentC', 'Test3', '62');

I will like to make them appears like a class list with columns of test scores  as shown:
Name, Test1, Test2, Test3
--------------------------------
StudentA,50,65,77
StudentB,45,68,73
StudentC,59,64,62

I did some searching (only managed to find 2 slighlty unrelated examples) and tried this query:
Select Name,
Case Test when 'Test1' then Marks end as Test1,
Case Test when 'Test2' then Marks end as Test2,
Case Test when 'Test3' then Marks end as Test3
from test group by Name

But the result I get from the above gives:
Name, Test1, Test2, Test3
--------------------------------
StudentA,50,NULL,NULL
StudentB,45,NULL,NULL
StudentC,59,NULL,NULL

Don't understand why 3rd and 4th columns shows NULL values.
Can someone help me with the correct query statement (in mysql) to show my desired list.
Thanks.

Comment
Watch Question
Data Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 7 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE