0

I want to get from this

+-----------+--------+---------+
| name      | color  | species |
+-----------+--------+---------+
| Tom       | grey   | cat     |
| Jerry     | brown  | mouse   |
| Sylvester | black  | cat     |
| Tweety    | yellow | bird    |
| Garfield  | orange | cat     |
| Odie      | yellow | dog     |
| Bugs      | grey   | rabbit  |
| Daffy     | black  | duck    |
+-----------+--------+---------+

to this

+---------+------+-------+-------+--------+--------+
| species | grey | brown | black | yellow | orange |
+---------+------+-------+-------+--------+--------+
| cat     |    1 |     0 |     1 |      0 |      1 |
| mouse   |    0 |     1 |     0 |      0 |      0 |
| bird    |    0 |     0 |     0 |      1 |      0 |
| dog     |    0 |     0 |     0 |      1 |      0 |
| rabbit  |    1 |     0 |     0 |      0 |      0 |
| duck    |    0 |     0 |     1 |      0 |      0 |
+---------+------+-------+-------+--------+--------+

in MySQL.

I know how to do it, if I know the colors in advance.

SELECT 
species,
COUNT(DISTINCT if (color='grey', name, null)) AS 'grey',
COUNT(DISTINCT if (color='brown', name, null)) AS 'brown',
COUNT(DISTINCT if (color='black', name, null)) AS 'black',
[...]
FROM animals
GROUP BY species

Is there a way to achieve this without adding a new COUNT-block for every color that might occur? There would be a problem, if the name of the color could not be used as column name.

The easiest way in SQL is something like

SELECT
species, color, COUNT(name)
FROM animals
GROUP BY species, color

but that requires more code to transform the data on the client side.

karbon
  • 21
  • 4
  • 3
    Even if it requires more code on the client side I would prefer not doing it in SQL. It is complicated and SQL should not be used to change the appearance of the data. It is designed to get data fast. – juergen d Nov 07 '19 at 15:09
  • 1
    Does this answer your question? [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) – Sebastian Kaczmarek Nov 07 '19 at 15:15
  • 1
    Seriously consider handling issues of data display in application code – Strawberry Nov 07 '19 at 15:39

1 Answers1

0

What you are looking for is called a "pivot". If you have ever used a "pivot table" in excel it is a very similar concept. Basically, you will automate the discover of row and column names and then fill out the table.

Checkout http://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/ and the "Dynamic pivot tables" section.

This kind of transformation isn't something I would recommend doing on the SQL side of your application. My personal opinion would be to keep all queries and data manipulation in your DB fully defined (not dynamically defined).

Kaspar Shahzada
  • 26
  • 4