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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nem Schlecht

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

(Extra ',' after Test3, but otherwise it works nicely).

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.