[MySQL] mysql 에서 컬럼과 로우 바꾸기,

행과 열 바꾸기

How to Transpose Rows to Columns Dynamically in MySQL

Sometimes, your data might stored in rows and you might want to report it as columns. In such cases, you will need to transpose rows into columns. Sometimes, even these rows can be variable. So you might know how many columns you need. In such cases, you need to transpose rows to columns dynamically. Since there is no built-in function to do that in MySQL, you need to accomplish it using an SQL query. Here’s an SQL query to dynamically transpose rows to columns in MySQL.

How to Transpose Rows to Columns Dynamically in MySQL

Here’s how to create dynamic pivot tables 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)

 [출처] https://ubiq.co/database-blog/transpose-rows-columns-dynamically-mysql/

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

Transpose rows to columns dynamically

If you already know which columns you would be creating beforehand, you can simply use a CASE statement to create a pivot table.

Since we don’t know which columns to be created, we will have to dynamically transpose rows to columns 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;

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   |
+------------+------------+-----------+------------+

This is how you can automate pivot table queries in MySQL and transpose rows to columns dynamically.

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>;

Also read: How to Unpivot Table in MySQL

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

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 transpose rows to columns dynamically in MySQL.

Here’s an example of pivot table created using Ubiq.

[출처] https://ubiq.co/database-blog/transpose-rows-columns-dynamically-mysql/