Mike Pala Mike Pala - 2 months ago 9
SQL Question

Restructure data with SQL

Let's say I have a table like this

Store | Fruit | Quantity
--------+-----------+-----------
Lincoln | cherry | 2
Lincoln | apple | 3
Lincoln | pear | 4
Abe | cherry | 1
Abe | apple | 2


I need an SQL query that would return this:

Store | Cherry | Apple | Pear
--------+--------+-------+------
Lincoln | 2 | 2 | 4
Abe | 1 | 2 |


It's fine if the "cherry, apple, and pear" columns are "hardcoded" in the query but what would be ideal (not sure if that's possible) is if when a new fruit pops up in the data a new column would be created by the SQL query

Answer

As your question is only tagged with , this is standard SQL:

select store, 
       sum(case when fruit = 'Cherry' then quantity end) as cherry_count, 
       sum(case when fruit = 'Apple' then quantity end) as apple_count, 
       sum(case when fruit = 'Pear' then quantity end) as pear_count 
from the_Table
group by store;