Mysql query with dynamic pivoting

Question

I have one table named grades like this:

  +--------+--------+-----------+
  | userid | itemid | finalgrade|
  +--------+--------+-----------+
  |  1     |   7    |    100    | 
  |  1     |   8    |    89     |    
  |  2     |   7    |    87     |     
  |  2     |   8    |    17     |    
  |  3     |   7    |    87     |   
  |  3     |   8    |    38     |   
  +--------+--------+-----------+

And I want to create a pivot query that changes me the table like this:

  +--------+---+---+
  | userid | 7 | 8 |
  +--------+---+---+
  |  1     |100| 89| 
  |  2     |87 | 17|        
  |  3     |87 | 38|    
  +--------+---+---+

I have one solution, using defined itemid like this one:

select *,
truncate (sum(finalgrade*(1-abs(sign(itemid-7)))),0) as 7,
truncate (sum(finalgrade*(1-abs(sign(itemid-8)))),0) as 8,
FROM grades
group by userid"

The problem is that I need the same query but with not defined itemid, dynamically creating columns.

fthiella · Accepted Answer · 2013-11-18 20:45:19Z

1

I think that you want a query like this:

SELECT userid,
  MAX(CASE WHEN itemid=7 THEN finalgrade END) as c7,
  MAX(CASE WHEN itemid=8 THEN finalgrade END) as c8,
FROM grades
GROUP BY userid

but you want it with dynamic values, and then you could use a prepared statement like this:

SELECT    
  CONCAT(    
    'SELECT userid,',    
    GROUP_CONCAT(CONCAT('MAX(CASE WHEN itemid=', itemid, ' THEN finalgrade END) AS c', itemid)),    
    ' FROM tablename GROUP BY userid')    
FROM (SELECT DISTINCT itemid FROM tablename) s INTO @sql;

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

You could use SUM instead of MAX, depending on how your data is structured.

answered Nov 18, 2013 at 20:45

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Enrique Berrueta Over a year ago

Thanks for the solution, but the query have a syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt' at line 8.

fthiella Over a year ago

@EnriqueBerrueta it looks correct to me: sqlfiddle.com/#!2/7e8f4/1 is itemid a INT column?

Enrique Berrueta Over a year ago

Yes, item id is INT. I was using sqlfiddle and surprise me because its correct, the problem is when i use it in phpmyadmin or in php.

fthiella Over a year ago

@EnriqueBerrueta do you have a lot of different itemids? group_concat has a length limit and i suspect that the query might have been cut... but this limit can be modified using set group_concat_max_len=<new limit>; its default is 1024 you could try with 10000...

Enrique Berrueta Over a year ago

Yes, this help me a little, but now the query gives me another error: Unknown prepared statement handler (stmt) given to EXECUTE

|

Collectives™ on Stack Overflow

Mysql query with dynamic pivoting

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related