MySQL Creating a pivot query


Example

MySQL does not provide a built-in way to create pivot queries. However, these can be created using prepared statements.

Assume the table tbl_values:

IdNameGroupValue
1PeteA10
2PeteB20
3JohnA10

Request: Create a query that shows the sum of Value for each Name; the Group must be column header and Name must be the row header.

-- 1. Create an expression that builds the columns
set @sql = (
    select group_concat(distinct 
        concat(
            "sum(case when `Group`='", Group, "' then `Value` end) as `", `Group`, "`"
        )
    ) 
    from tbl_values
);

-- 2. Complete the SQL instruction
set @sql = concat("select Name, ", @sql, " from tbl_values group by `Name`");

-- 3. Create a prepared statement
prepare stmt from @sql;

-- 4. Execute the prepared statement
execute stmt;

Result:

NameAB
John10NULL
Pete1020

Important: Deallocate the prepared statement once it's no longer needed:

deallocate prepare stmt;

Example on SQL Fiddle