MySQL pivot multi-row table to unique row table

Question

I have the following table of results data in MySQL. I would like to pivot this around from a row per UPN per Subject Grade to a row per UPN so the table below:

UPN        Collection      Subject        Grade
1          Target          English        5
1          Current         English        6
1          Target          Maths          7
1          Current         Maths          7
1          Target          Art            6
1          Current         Art            6
2          Target          English        5
2          Current         English        5
2          Target          Maths          6
2          Current         Maths          7
2          Target          History        6
2          Current         History        5

Should pivot around to the table below:

UPN    English Current    English Target    Maths Current    Maths Target    Art Current    Art Target    History Current    History Target
1      6                  5                 7                7               6              6             NULL               NULL
2      5                  5                 7                6               NULL           NULL          5                  6

Please note that in the second table the UPN row must become unique, so no duplicate UPN rows containing NULLs.

Also where a UPN doesn't have a student then the cell value should be NULL.

SQL Fiddle

What did you try to achieve that? Please share an sql fiddle too — 1000111
– 1000111, Commented Aug 29, 2016 at 9:34
I'm essentially trying to re-orientate a long list of rows of values into a broadsheet view with columns going across the top. — Matt
– Matt, Commented Aug 29, 2016 at 9:39
May you will get answer from this: stackoverflow.com/questions/39138468/… — Manish
– Manish, Commented Aug 29, 2016 at 9:53
@Matt in the fiddle you are supposed to show your attempts to do anything on this task! :) — user2399035
– user2399035, Commented Aug 29, 2016 at 9:54

krunal nerikar · Accepted Answer · 2016-08-29 10:24:39Z

Try below query

SELECT t1.UPN,
IFNULL((SELECT MAX(t2.Grade) FROM `results` AS t2 WHERE t2.UPN = t1.UPN AND t2.`Collection` = 'Current' AND t2.`Subject` = 'English'),NULL) AS 'English Current',
IFNULL((SELECT MAX(t2.Grade) FROM `results` AS t2 WHERE t2.UPN = t1.UPN AND t2.`Collection` = 'Target' AND t2.`Subject` = 'English'),NULL) AS 'English Target', 
IFNULL((SELECT MAX(t2.Grade) FROM `results` AS t2 WHERE t2.UPN = t1.UPN AND t2.`Collection` = 'Current' AND t2.`Subject` = 'Maths'),NULL) AS 'Maths Current',
IFNULL((SELECT MAX(t2.Grade) FROM `results` AS t2 WHERE t2.UPN = t1.UPN AND t2.`Collection` = 'Target' AND t2.`Subject` = 'Maths'),NULL) AS 'Maths Target',
IFNULL((SELECT MAX(t2.Grade) FROM `results` AS t2 WHERE t2.UPN = t1.UPN AND t2.`Collection` = 'Current' AND t2.`Subject` = 'Art'),NULL) AS 'Art Current',
IFNULL((SELECT MAX(t2.Grade) FROM `results` AS t2 WHERE t2.UPN = t1.UPN AND t2.`Collection` = 'Target' AND t2.`Subject` = 'Art'),NULL) AS 'Art Target',
IFNULL((SELECT MAX(t2.Grade) FROM `results` AS t2 WHERE t2.UPN = t1.UPN AND t2.`Collection` = 'Current' AND t2.`Subject` = 'History'),NULL) AS 'History Current',
IFNULL((SELECT MAX(t2.Grade) FROM `results` AS t2 WHERE t2.UPN = t1.UPN AND t2.`Collection` = 'Target' AND t2.`Subject` = 'History'),NULL) AS 'History Target'
FROM `results` AS t1 GROUP BY t1.`UPN`

1000111 · Accepted Answer · 2016-08-29 09:54:53Z

1

This is a typical pviot table problem. Please check the query given below:

SET @sql := '';

SELECT 
CONCAT('SELECT upn, ',
GROUP_CONCAT(t.sql_code),
' FROM results GROUP BY upn;'
) INTO @sql
FROM 
(
SELECT 
GROUP_CONCAT('MAX(CASE WHEN Collection =\'', Collection ,'\' AND Subject =\'', Subject ,'\' THEN Grade END) AS \'',CONCAT(Collection,' ',Subject),'\'') AS sql_code
FROM results
GROUP BY Collection,Subject
) AS t;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;

answered Aug 29, 2016 at 9:54

1000111

13.5k2 gold badges32 silver badges43 bronze badges

Comments

Manish · Accepted Answer · 2016-08-29 10:34:40Z

@Matt I think this will solve your problem.

You will see in SQL Fiddle

SELECT tb.UPN,(SELECT MAX(tb1.Grade) FROM `results` AS tb1 WHERE tb1.UPN = tb.UPN AND tb1.`Collection` = 'Current' AND tb1.`Subject` = 'English') AS 'English Current',(SELECT MAX(tb1.Grade) FROM `results` AS tb1 WHERE tb1.UPN = tb.UPN AND tb1.`Collection` = 'Target' AND tb1.`Subject` = 'English') AS 'English Target', 
(SELECT MAX(tb1.Grade) FROM `results` AS tb1 WHERE tb1.UPN = tb.UPN AND tb1.`Collection` = 'Current' AND tb1.`Subject` = 'Maths') AS 'Maths Current',
(SELECT MAX(tb1.Grade) FROM `results` AS tb1 WHERE tb1.UPN = tb.UPN AND tb1.`Collection` = 'Target' AND tb1.`Subject` = 'Maths') AS 'Maths Target',
(SELECT MAX(tb1.Grade) FROM `results` AS tb1 WHERE tb1.UPN = tb.UPN AND tb1.`Collection` = 'Current' AND tb1.`Subject` = 'Art') AS 'Art Current',
(SELECT MAX(tb1.Grade) FROM `results` AS tb1 WHERE tb1.UPN = tb.UPN AND tb1.`Collection` = 'Target' AND tb1.`Subject` = 'Art') AS 'Art Target',
(SELECT MAX(tb1.Grade) FROM `results` AS tb1 WHERE tb1.UPN = tb.UPN AND tb1.`Collection` = 'Current' AND tb1.`Subject` = 'History') AS 'History Current',
(SELECT MAX(tb1.Grade) FROM `results` AS tb1 WHERE tb1.UPN = tb.UPN AND tb1.`Collection` = 'Target' AND tb1.`Subject` = 'History') AS 'History Target'
FROM `results` AS tb GROUP BY tb.`UPN`

Collectives™ on Stack Overflow

MySQL pivot multi-row table to unique row table

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related