MySQL GroupBy and shows it horizontally

Question

Suppose I have a table below:

1) tblScore ============================ Date VendorID Score ============================ 12/09/01 12001 A 12/09/01 12001 A 12/09/01 12002 B 12/09/02 12003 C 12/09/02 12003 A 12/09/03 12001 C ============================ 

I have this query:

 SELECT ts.VendorID, ts.Score, COUNT(*) FROM trxscore ts GROUP BY ts.VendorID, ts.Score ORDER BY ts.VendorID, ts.Score 

But how to show the table as:

 =========================== VendorID ABC =========================== 12001 2 0 1 12002 0 1 0 12003 1 0 1 =========================== 

And, is it possible to get the average value from the text? that is, VendorID 12001 should get the average value of A. Thank you ...

Answer 1

Try it,

 SELECT VendorID, SUM(CASE WHEN Score = 'A' THEN 1 ELSE 0 END), SUM(CASE WHEN Score = 'B' THEN 1 ELSE 0 END), SUM(CASE WHEN Score = 'C' THEN 1 ELSE 0 END) FROM tableName GROUP BY VendorID 

SQLFiddle Demo