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