Creating a pivot query

suggest change

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:

Id | Name | Group | Value | —— | —— | —– | —– | 1 | Pete | A | 10 | 2 | Pete | B | 20 | 3 | John | A | 10 |

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:

Name | A | B | –– | — | –– | John | 10 | NULL | Pete | 10 | 20 |

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

deallocate prepare stmt;

Example on SQL Fiddle

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:



Table Of Contents