Grouping SQL Result

Question

I have table like this:

id | Name  | Buy
---+-------+-----
 1 | Alice | Apple
 2 | Alice | Banana
 3 | Alice | Apple
 4 | Bob   | Apple

How to write one line SQL command that showing result like below?

Name  | Apple | Banana
------+-------+-------
Alice |     2 |      1
Bob   |     1 |      0

Answer 1

use case when

select Name,sum(case when Buy='Apple' then 1 else0 end) as Apple,
sum(case when Buy='Banana' then 1 else 0 end) as Banana
from yourtable
group by Name

http://sqlfiddle.com/#!9/5ab22a/2

Name    Apple   Banana
Alice   2        1
Bob     1        0