Avatar of LimMH
LimMH
 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.

DatabasesMySQL ServerSQL

Avatar of undefined
Last Comment
k_murli_krishna

8/22/2022 - Mon
Nem Schlecht

First off, *THANK YOU* for you posting your SQL!  So much more helpful!  The problem is the GROUP BY() function, which in essence only returns these values:

('StudentA', 'Test1', '50'),
('StudentB', 'Test1', '45'),
('StudentC', 'Test1', '59'),

Because those are the first instances of each of student.

Still working on a solution.....
Sharath S

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

Open in new window

SOLUTION
Nem Schlecht

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Nem Schlecht

Nice, Sharath_123, use a grouping function... makes perfect sense.

(Extra ',' after Test3, but otherwise it works nicely).
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Sharath S

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
LimMH

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

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.