Counts grouped by two columns output into columns & rows-CodePudding

Let's say I've got some data with two columns: name and ice cream purchases.

Joe  | Chocolate
Mary | Vanilla
Beth | Rocky Road
Fred | Vanilla
Mary | Rocky Road
Joe  | Vanilla
Joe  | Chocolate
etc...

What I want to do is get the count, grouped by both columns. I know how to do this so that it will output into three columns: name, flavor, and count. However, what I want to do is output it with the names as the rows and the flavors as the columns, like this:

       Vanilla | Chocolate | Rocky Road
Joe  |    1    |     2     |      0
Mary |    1    |     0     |      1
Beth |    0    |     0     |      1
Fred |    1    |     0     |      0

Is there a way to do this with just a SQL query? (The database is in SQLite, if that makes any difference.)

CodePudding user response：

Use conditional aggregation:

SELECT name,
       SUM(ice = 'Vanilla') Vanilla,
       SUM(ice = 'Chocolate') Chocolate,
       SUM(ice = 'Rocky Road') "Rocky Road"
FROM tablename
GROUP BY name;

See the demo.