1
The only way I know to do this exclusively with SQL would be something like this:
SELECT
CONCAT (
'SELECT name, ',
GROUP_CONCAT(
DISTINCT CONCAT(
'(',
'SELECT IFNULL(SUM(value), 0) '
'FROM tabela b ',
'WHERE b.key_id = "', key_id, '"',
'AND b.name = a.name'
') ', key_id, ' '
)
), ' '
'FROM tabela a ',
'GROUP BY name'
) INTO @sql
FROM tabela;
PREPARE stmt FROM @sql;
EXECUTE stmt;
Note that there are 3 statements (the first generates a query, the second "prepares" the statement, and the third executes the query).
Although this works, I would only recommend using this if it is to generate some report, or for a very small database, never in an application, because for a few hundred key_ids, the BD would no longer swallow well.
The best solution would be to get all the data from the table and group according to what you need on the application side (using Node/PHP/C#/Java, whatever).
Could improve your response using formatting see How to format my posts using Markdown or HTML?.
– NoobSaibot
This results in multiple results with the same "Name".
– Josiel Souza
Certainly. But this is the correct result using only SQL. You must then work the resultset to present the data without repeating the "name". It is good practice to keep the presentation layer source code separate.
– Caiuby Freitas