Re-organizing table data by column

Question

I have a list of data that I'd like to convert from one layout to another. I'm not entirely sure about the best way to go about converting from one layout to another. I've thought of scraping one "cheesetype" at a time and then joining the results. I'm hoping there's a more elegant solution. Here's what I've got :

| BurgerType   | CheeseType | Slices |
|--------------|------------|--------|
| Cheeseburger | Provolone  | 2      |
| Cheeseburger | Cheddar    | 3      |
| Cheeseburger | Bleu       | 1      |
| Loosey       | Provolone  | 4      |
| Loosey       | Cheddar    | 4      |
| BlackBleu    | Bleu       | 2      |
| MONSTERNOM   | Brie       | 2      |
| MONSTERNOM   | Cheddar    | 1      |

I'd like to convert it to look something like this :

| BurgerType   | Provolone | Cheddar | Bleu | Brie |
|--------------|-----------|---------|------|------|
| Cheeseburger | 2         | 3       | 1    | 0    |
| Loosey       | 4         | 4       | 0    | 0    |
| BlackBleu    | 0         | 0       | 2    | 2    |
| MONSTERNOM   | 0         | 1       | 0    | 2    |

Welcome to Stackoverflow! I believe you are looking for a `PIVOT`. Check out [this question/answer](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — JNevill, Aug 27 '18 at 19:58
Seriously consider handling issues of data display in application code — Strawberry, Aug 27 '18 at 21:56

score 3 · Answer 1 · answered Aug 27 '18 at 20:00

You can use conditional aggregation. The CASE only returns the count of slices, when the type of cheese matches, otherwise 0. The max() then takes the largest number returned, which is the count of slices for this type of cheese and burger.

SELECT burgertyp,
       max(CASE
             WHEN cheesetype = 'Provolone' THEN
               slices
             ELSE
               0) provolone,
       ...
       max(CASE
             WHEN cheesetype = 'Brie' THEN
               slices
             ELSE
               0) brie
       FROM elbat
       GROUP BY burgertype;

Re-organizing table data by column

1 Answers1