SQL Pivot (여러개의 Row 를 하나의 Row 의 Column 들로 변경하기)

Data Pivot

---

여러개의 로우를 가진 값을 하나의 로우에 여러개의 컬럼으로 바꾸는 예제이다.

주어진 테이블을 다음과 같다.

Table 1
|ID | Name |
|—|—| |1 | Jim |
|2 | Bob |
|3 | John |

Table 2
|ID | key | value | |—|—|—| |1 | address | “X Street” | |1 | city | “NY” | |1 | region | “NY” | |1 | country | “USA” | |1 | postal_code | | |1 | phone | “123456789” |

위 두개의 테이블을 다음과 같이 보여주고 싶다.

Result | ID | Name | address | city | region | country | postal_code | phone | |—|—|—|—|—|—|—|—| |1 | Jim | X Street | NY | NY | USA | NULL | 123456789 | |2 | Bob | NULL | NULL | NULL | NULL | NULL | NULL | |3 | John | NULL | NULL | NULL | NULL | NULL | NULL |

방법은 찾아보니 3가지 정도가 있다. 첫번째는 GROUP BY 와 MAX 함수를 이용한 방법. 단, 이방법은 같은 컬럼에 값이 여러개일 경우 오류를 발생할 수 있고 또한 너무 많은 로우를 변환하고자 할때에는 코드가 길어질 수 있다.

SELECT t1.id,
       t1.name,
       MAX(CASE WHEN t2.`key` = 'address' THEN t2.value END) address,
       MAX(CASE WHEN t2.`key` = 'city' THEN t2.value END) city,
       MAX(CASE WHEN t2.`key` = 'region' THEN t2.value END) region,
       MAX(CASE WHEN t2.`key` = 'country' THEN t2.value END) country,
       MAX(CASE WHEN t2.`key` = 'postal_code' THEN t2.value END) postal_code,
       MAX(CASE WHEN t2.`key` = 'phone' THEN t2.value END) phone
  FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id
 GROUP BY t1.id, t1.name

두번째 방법은 첫번째 방법의 단점 중 하나인 값이 여러개를 가지는 컬럼이 존재할 경우를 대비한 방법이다. 다만 이방법도 역시 로우가 많아질수록 코드가 길어지고 관리가 힘들다는 단점이 있다.

SELECT t1.id,
       t1.name,
       t2a.value address,
       t2c.value city,
       t2r.value region,
       t2y.value country,
       t2pc.value postal_code,
       t2p.value phone
  FROM table1 t1 
    LEFT JOIN table2 t2a ON t1.id = t2a.id AND t2a.`key` = 'address'
    LEFT JOIN table2 t2c ON t1.id = t2c.id AND t2c.`key` = 'city' 
    LEFT JOIN table2 t2r ON t1.id = t2r.id AND t2c.`key` = 'region' 
    LEFT JOIN table2 t2y ON t1.id = t2y.id AND t2c.`key` = 'country' 
    LEFT JOIN table2 t2pc ON t1.id = t2pc.id AND t2pc.`key` = 'postal_code' 
    LEFT JOIN table2 t2p ON t1.id = t2p.id AND t2p.`key` = 'phone';

마지막 방법은 위의 단점들을 모두 해결할 수 있는 방법이다. 단, 여기에는 프로시져 또는 함수를 알아야한다는 전제조건이 필요하다.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when t2.`key` = ''',
      `key`,
      ''' then t2.value end) AS `',
      `key`, '`'
    )
  ) INTO @sql
from Table2;

SET @sql 
    = CONCAT('SELECT t1.id, t1.name, ', @sql, ' 
              from table1 t1
              left join table2 t2
                on t1.id = t2.id
              group by t1.id, t1.name;');

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

그러면 결과는 다음과 같이 나온다.

Query Result | ID | NAME | ADDRESS | CITY | REGION | COUNTRY | POSTAL_CODE | PHONE | |—-|——|———-|——–|——–|———|————-|———–| | 1 | Jim | X Street | NY | (null) | (null) | (null) | 123456789 | | 2 | Bob | (null) | (null) | (null) | (null) | (null) | (null) | | 3 | John | (null) | (null) | (null) | (null) | (null) | (null) |

참고: MySQL Pivot