asked on
MYSQL Pivot Table
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.
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.
DatabasesMySQL ServerSQL
Last Comment
k_murli_krishna
try this query.
select Name,
max(case Test when 'Test1' then Marks end) as Test1,
max(case Test when 'Test2' then Marks end) as Test2,
max(case Test when 'Test3' then Marks end) as Test3,
from test
group by Name
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Nice, Sharath_123, use a grouping function... makes perfect sense.
(Extra ',' after Test3, but otherwise it works nicely).
(Extra ',' after Test3, but otherwise it works nicely).
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Both answers gave me the require table. Thanks. but sharath's gets it done in 0.0014 sec while the nemws1's does more queries and gets it done in 0.0065 sec. Rightly I will prefer sharath's but I like to thank nemws1's for another perspective to getting the anwser. So I share split it 400 by 100 for the effort. Many thanks.
Try this:
SELECT Name,
CASE WHEN MAX(Test1) IS NOT NULL THEN MAX(Test1) END as Test1,
CASE WHEN MAX(Test2) IS NOT NULL THEN MAX(Test2) END as Test2,
CASE WHEN MAX(Test3) IS NOT NULL THEN MAX(Test3) END as Test3
FROM(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)iq
GROUP BY Name;
This is from SQL Server since that is what I have. MySQL maybe simpler as per your example so nicely acknowledged by previous expert.
SELECT Name,
CASE WHEN Test1 IS NOT NULL THEN Test1 END as Test1,
CASE WHENTest2 IS NOT NULL THENTest2 END as Test2,
CASE WHEN Test3 IS NOT NULL THEN Test3 END as Test3
FROM(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)iq
GROUP BY Name;
Please use ) AS iq instead of )iq if required or no iq at all i.e. whatever syntax is particular to MySQL.
SELECT Name,
CASE WHEN MAX(Test1) IS NOT NULL THEN MAX(Test1) END as Test1,
CASE WHEN MAX(Test2) IS NOT NULL THEN MAX(Test2) END as Test2,
CASE WHEN MAX(Test3) IS NOT NULL THEN MAX(Test3) END as Test3
FROM(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)iq
GROUP BY Name;
This is from SQL Server since that is what I have. MySQL maybe simpler as per your example so nicely acknowledged by previous expert.
SELECT Name,
CASE WHEN Test1 IS NOT NULL THEN Test1 END as Test1,
CASE WHENTest2 IS NOT NULL THENTest2 END as Test2,
CASE WHEN Test3 IS NOT NULL THEN Test3 END as Test3
FROM(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)iq
GROUP BY Name;
Please use ) AS iq instead of )iq if required or no iq at all i.e. whatever syntax is particular to MySQL.
('StudentA', 'Test1', '50'),
('StudentB', 'Test1', '45'),
('StudentC', 'Test1', '59'),
Because those are the first instances of each of student.
Still working on a solution.....