MYSQL - Rows to Columns

Question

Hi,

I tried to search posts, but I only found solutions for SQL Server/Access. I need a solution to MYSQL (5.X).

I have a table with 3 columns: hostid, itemname, itemvalue. If I do a select, it will return

1  A  10
1  B  3
2  A  9
2  C  40

How do I query the database to return something like

   A    B    C
1  10   3
2  9         40

Answer 1

I found this post

Spinning rows into columns, in a view. Is it possible?

Hope that helps

Answer 2

SELECT hostid, sum( if( itemname = 'A', itemvalue, 0 ) ) AS A,  
sum( if( itemname = 'B', itemvalue, 0 ) ) AS B, 
sum( if( itemname = 'C', itemvalue, 0 ) ) AS C 
FROM bob GROUP BY hostid