What are some ways to pivot rows into columns in MySQL?

There are several ways to implement the conversion of rows to columns in MySQL.

1. With the CASE statement, you can transform row data into column data based on conditions.

SELECT
    id,
    MAX(CASE WHEN attribute = 'attribute1' THEN value END) AS attribute1,
    MAX(CASE WHEN attribute = 'attribute2' THEN value END) AS attribute2,
    MAX(CASE WHEN attribute = 'attribute3' THEN value END) AS attribute3
FROM
    table
GROUP BY
    id;

1. Pivot function: Although MySQL does not have a built-in pivot function, it is possible to simulate the functionality using subqueries and aggregate functions.

SELECT
    id,
    MAX(CASE WHEN attribute = 'attribute1' THEN value END) AS attribute1,
    MAX(CASE WHEN attribute = 'attribute2' THEN value END) AS attribute2,
    MAX(CASE WHEN attribute = 'attribute3' THEN value END) AS attribute3
FROM
    (SELECT id, attribute, value FROM table) AS t
GROUP BY
    id;

1. Using the GROUP_CONCAT function: It allows you to merge multiple rows of data into one string, which can then be split into multiple columns using a subquery.

SELECT
    id,
    SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN attribute = 'attribute1' THEN value END), ',', 1) AS attribute1,
    SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN attribute = 'attribute2' THEN value END), ',', 1) AS attribute2,
    SUBSTRING_INDEX(GROUP_CONCAT(CASE WHEN attribute = 'attribute3' THEN value END), ',', 1) AS attribute3
FROM
    table
GROUP BY
    id;

The above are several common ways to pivot rows to columns in MySQL, and you can choose the most suitable one based on your actual needs.