How to Display Row Values as Columns in MySQL

Last updated on May 17th, 2020 at 06:19 am

Sometimes, you may need to transpose rows into columns or create pivot tables for reporting purposes. Since there is no built-in function to achieve pivot in MySQL, you need to accomplish it via SQL query to create pivot report table. Let’s see how to display row values as columns in MySQL.

Table of Contents

How to Display Row Values as Columns in MySQL

Let’s say you have the following table.

CREATE TABLE Meeting
(
    ID INT,
    Meeting_id INT,
    field_key VARCHAR(100),
    field_value VARCHAR(100)
);

INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (1, 1,'first_name' , 'Alec');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (2, 1,'last_name' , 'Jones');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (3, 1,'occupation' , 'engineer');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (4,2,'first_name' , 'John');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (5,2,'last_name' , 'Doe');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (6,2,'occupation' , 'engineer');

+------+------------+------------+-------------+
| ID   | Meeting_id | field_key  | field_value |
+------+------------+------------+-------------+
|    1 |          1 | first_name | Alec        |
|    2 |          1 | last_name  | Jones       |
|    3 |          1 | occupation | engineer    |
|    4 |          2 | first_name | John        |
|    5 |          2 | last_name  | Doe         |
|    6 |          2 | occupation | engineer    |
+------+------------+------------+-------------+

Let’s say you want to transpose rows to columns dynamically, such that a new column is created for each unique value in field_key column, that is (first_name, last_name, occupation)

+------------+-------------+-------------+-------------+
| Meeting_id | first_name  |  last_name  |  occupation |
+------------+-------------+-------------+-------------+
|          1 |       Alec  | Jones       | engineer    |
|          2 |       John  | Doe         | engineer    |
+------------+-------------+-------------+-------------+

Display Row Values as Columns in MySQL

If you already know which columns you want to create, you can create a pivot table using CASE statements, as shown below, to display row values as columns in MySQL.

mysql> select meeting_Id,
         max(case when (field_key='first_name') then field_value else NULL end) as 'first_name',
         max(case when (field_key='last_name') then field_value else NULL end) as 'last_name',
         max(case when (field_key='occupation') then field_value else NULL end) as 'occupation'
         from meeting
         group by meeting_Id
         order by meeting_Id;
+------------+------------+-----------+------------+
| meeting_Id | first_name | last_name | occupation |
+------------+------------+-----------+------------+
|          1 | Alec       | Jones     | engineer   |
|          2 | John       | Doe       | engineer   |
+------------+------------+-----------+------------+

Display Row Values as Columns in MySQL Dynamically

If you don’t know the column names before hand, or want to display row values as columns in MySQL dynamically, you can create dynamic pivot tables in MySQL using GROUP_CONCAT function, as shown below.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when field_key = ''',
      field_key,
      ''' then field_value end) ',
      field_key
    )
  ) INTO @sql
FROM
  Meeting;
SET @sql = CONCAT('SELECT Meeting_id, ', @sql, ' 
                  FROM Meeting 
                   GROUP BY Meeting_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Also read: How to Calculate Median in MySQL

GROUP_CONCAT allows you to concatenate field_key values from multiple rows into a single string. In the above query, we use GROUP_CONCAT to dynamically create CASE statements, based on the unique values in field_key column and store that string in @sql variable, which is then used to create our select query.

+------------+------------+-----------+------------+
| Meeting_id | first_name | last_name | occupation |
+------------+------------+-----------+------------+
|          1 | Alec       | Jones     | engineer   |
|          2 | John       | Doe       | engineer   |
+------------+------------+-----------+------------+

You can customize the above query as per your requirements by adding WHERE clause or JOINS.

If you want to transpose only select row values as columns, you can add WHERE clause in your 1st select GROUP_CONCAT statement.

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when field_key = ''',
      field_key,
      ''' then field_value end) ',
      field_key
    )
  ) INTO @sql
FROM
  Meeting
WHERE <condition>;

If you want to filter rows in your final pivot table, you can add the WHERE clause in your SET statement.

SET @sql = CONCAT('SELECT Meeting_id, ', @sql, ' 
                  FROM Meeting WHERE <condition>
                   GROUP BY Meeting_id');

Similarly, you can also apply JOINS in your SQL query while you display row values as columns in MySQL.

After you convert row to column in MySQL, you can use a charting tool to plot the result in a table. Here’s an example of a pivot table created using Ubiq.