Zenn
📌

MySQL (MariaDB) で行と列を入れ替える

2020/11/17に公開
MySQL
SQL
MariaDB
#
groupby
tech

結論

CASE WHENで行を列に変換し、GROUP_CONCATで連結する。

テーブルと得たい結果のイメージ

以下のテーブルがあったとする。

CREATE TABLE `address_entry` (
    `id` SERIAL PRIMARY KEY
    ,`address_id` bigint unsigned NOT NULL
    ,`address_type` text NOT NULL
    ,`value` text NOT NULL
    /* ,FOREIGN KEY (`address_id`) REFERENCES `address` (`id`) */
) ENGINE=InnoDB DEFAULT CHARSET = utf8;

INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (1, 'COUNTRY', 'USA');
INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (1, 'STREET', 'One Apple Park Way');
INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (1, 'CITY', 'Cupertino');
INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (1, 'STATE', 'CA');
INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (1, 'ZIP', '95014');
INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (2, 'COUNTRY', '日本国');
INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (2, 'PREFECTURE', '東京都');
INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (2, 'CITY', '中央区');
INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (2, 'STREET', '銀座3-5-12');
INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (2, 'BUILDING', 'サヱグサビル本館');
INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (2, 'PHONE', '03-5159-8200');
INSERT INTO `address_entry` (`address_id`, `address_type`, `value`) VALUES (2, 'POSTAL_CODE', '104-0061');

SELECT * FROM `address_entry`;
+----+------------+--------------+--------------------------+
| id | address_id | address_type | value                    |
+----+------------+--------------+--------------------------+
|  1 |          1 | COUNTRY      | USA                      |
|  2 |          1 | STREET       | One Apple Park Way       |
|  3 |          1 | CITY         | Cupertino                |
|  4 |          1 | STATE        | CA                       |
|  5 |          1 | ZIP          | 95014                    |
|  6 |          2 | COUNTRY      | 日本国                   |
|  7 |          2 | PREFECTURE   | 東京都                   |
|  8 |          2 | CITY         | 中央区                   |
|  9 |          2 | STREET       | 銀座3-5-12               |
| 10 |          2 | BUILDING     | サヱグサビル本館         |
| 11 |          2 | PHONE        | 03-5159-8200             |
| 12 |          2 | POSTAL_CODE  | 104-0061                 |
+----+------------+--------------+--------------------------+

このテーブルを元に、address_typeを列に展開した表が得たい。

+------------+-----------+--------------------+-----------+-------+-------+------------+--------------------------+--------------+-------------+
| address_id | country   | street             | city      | state | zip   | prefecture | building                 | phone        | postal_code |
+------------+-----------+--------------------+-----------+-------+-------+------------+--------------------------+--------------+-------------+
|          1 | USA       | One Apple Park Way | Cupertino | CA    | 95014 |            |                          |              |             |
|          2 | 日本国    | 銀座3-5-12         | 中央区    |       |       | 東京都     | サヱグサビル本館         | 03-5159-8200 | 104-0061    |
+------------+-----------+--------------------+-----------+-------+-------+------------+--------------------------+--------------+-------------+

CASE WHENとGROUP BYを使った失敗例

CASE WHENとGROUP BYを使えば良いのでは?

SELECT
    `address_entry`.`address_id`
     ,CASE WHEN `address_entry`.`address_type`='COUNTRY'     THEN `address_entry`.`value` ELSE '' END AS `country`
     ,CASE WHEN `address_entry`.`address_type`='STREET'      THEN `address_entry`.`value` ELSE '' END AS `street`
     ,CASE WHEN `address_entry`.`address_type`='CITY'        THEN `address_entry`.`value` ELSE '' END AS `city`
     ,CASE WHEN `address_entry`.`address_type`='STATE'       THEN `address_entry`.`value` ELSE '' END AS `state`
     ,CASE WHEN `address_entry`.`address_type`='ZIP'         THEN `address_entry`.`value` ELSE '' END AS `zip`
     ,CASE WHEN `address_entry`.`address_type`='PREFECTURE'  THEN `address_entry`.`value` ELSE '' END AS `prefecture`
     ,CASE WHEN `address_entry`.`address_type`='BUILDING'    THEN `address_entry`.`value` ELSE '' END AS `building`
     ,CASE WHEN `address_entry`.`address_type`='PHONE'       THEN `address_entry`.`value` ELSE '' END AS `phone`
     ,CASE WHEN `address_entry`.`address_type`='POSTAL_CODE' THEN `address_entry`.`value` ELSE '' END AS `postal_code`
FROM
    `address_entry`
GROUP BY
    `address_entry`.`address_id`
;

GROUP BYしてしまうと最初の行しかマッチしなくなる。

+------------+-----------+--------+------+-------+-----+------------+----------+-------+-------------+
| address_id | country   | street | city | state | zip | prefecture | building | phone | postal_code |
+------------+-----------+--------+------+-------+-----+------------+----------+-------+-------------+
|          1 | USA       |        |      |       |     |            |          |       |             |
|          2 | 日本国    |        |      |       |     |            |          |       |             |
+------------+-----------+--------+------+-------+-----+------------+----------+-------+-------------+
2 rows in set (0.002 sec)

ではどうしたら良いのだろうか。

GROUP BYをやめる

GROUP BYを削除してみる。

SELECT
    `address_entry`.`address_id`
     ,CASE WHEN `address_entry`.`address_type`='COUNTRY'     THEN `address_entry`.`value` ELSE '' END AS `country`
     ,CASE WHEN `address_entry`.`address_type`='STREET'      THEN `address_entry`.`value` ELSE '' END AS `street`
     ,CASE WHEN `address_entry`.`address_type`='CITY'        THEN `address_entry`.`value` ELSE '' END AS `city`
     ,CASE WHEN `address_entry`.`address_type`='STATE'       THEN `address_entry`.`value` ELSE '' END AS `state`
     ,CASE WHEN `address_entry`.`address_type`='ZIP'         THEN `address_entry`.`value` ELSE '' END AS `zip`
     ,CASE WHEN `address_entry`.`address_type`='PREFECTURE'  THEN `address_entry`.`value` ELSE '' END AS `prefecture`
     ,CASE WHEN `address_entry`.`address_type`='BUILDING'    THEN `address_entry`.`value` ELSE '' END AS `building`
     ,CASE WHEN `address_entry`.`address_type`='PHONE'       THEN `address_entry`.`value` ELSE '' END AS `phone`
     ,CASE WHEN `address_entry`.`address_type`='POSTAL_CODE' THEN `address_entry`.`value` ELSE '' END AS `postal_code`
FROM
    `address_entry`
;

以下の表が得られる。

`+------------+-----------+--------------------+-----------+-------+-------+------------+--------------------------+--------------+-------------+
| address_id | country   | street             | city      | state | zip   | prefecture | building                 | phone        | postal_code |
+------------+-----------+--------------------+-----------+-------+-------+------------+--------------------------+--------------+-------------+
|          1 | USA       |                    |           |       |       |            |                          |              |             |
|          1 |           | One Apple Park Way |           |       |       |            |                          |              |             |
|          1 |           |                    | Cupertino |       |       |            |                          |              |             |
|          1 |           |                    |           | CA    |       |            |                          |              |             |
|          1 |           |                    |           |       | 95014 |            |                          |              |             |
|          2 | 日本国    |                    |           |       |       |            |                          |              |             |
|          2 |           |                    |           |       |       | 東京都     |                          |              |             |
|          2 |           |                    | 中央区    |       |       |            |                          |              |             |
|          2 |           | 銀座3-5-12         |           |       |       |            |                          |              |             |
|          2 |           |                    |           |       |       |            | サヱグサビル本館         |              |             |
|          2 |           |                    |           |       |       |            |                          | 03-5159-8200 |             |
|          2 |           |                    |           |       |       |            |                          |              | 104-0061    |
+------------+-----------+--------------------+-----------+-------+-------+------------+--------------------------+--------------+-------------+
12 rows in set (0.001 sec)

この表をGROUP BYすれば良いのでは?

GROUP_CONCATで連結する

MySQL (MariaDB) でしか使えない集計関数GROUP_CONCATでグループ内の文字列を連結する。

SELECT
    tmp_entry.address_id
     ,GROUP_CONCAT(`tmp_entry`.`country`     SEPARATOR '') AS `country`
     ,GROUP_CONCAT(`tmp_entry`.`street`      SEPARATOR '') AS `street`
     ,GROUP_CONCAT(`tmp_entry`.`city`        SEPARATOR '') AS `city`
     ,GROUP_CONCAT(`tmp_entry`.`state`       SEPARATOR '') AS `state`
     ,GROUP_CONCAT(`tmp_entry`.`zip`         SEPARATOR '') AS `zip`
     ,GROUP_CONCAT(`tmp_entry`.`prefecture`  SEPARATOR '') AS `prefecture`
     ,GROUP_CONCAT(`tmp_entry`.`building`    SEPARATOR '') AS `building`
     ,GROUP_CONCAT(`tmp_entry`.`phone`       SEPARATOR '') AS `phone`
     ,GROUP_CONCAT(`tmp_entry`.`postal_code` SEPARATOR '') AS `postal_code`
FROM
    (SELECT
        `address_entry`.`address_id`
         ,CASE WHEN `address_entry`.`address_type`='COUNTRY'     THEN `address_entry`.`value` ELSE '' END AS `country`
         ,CASE WHEN `address_entry`.`address_type`='STREET'      THEN `address_entry`.`value` ELSE '' END AS `street`
         ,CASE WHEN `address_entry`.`address_type`='CITY'        THEN `address_entry`.`value` ELSE '' END AS `city`
         ,CASE WHEN `address_entry`.`address_type`='STATE'       THEN `address_entry`.`value` ELSE '' END AS `state`
         ,CASE WHEN `address_entry`.`address_type`='ZIP'         THEN `address_entry`.`value` ELSE '' END AS `zip`
         ,CASE WHEN `address_entry`.`address_type`='PREFECTURE'  THEN `address_entry`.`value` ELSE '' END AS `prefecture`
         ,CASE WHEN `address_entry`.`address_type`='BUILDING'    THEN `address_entry`.`value` ELSE '' END AS `building`
         ,CASE WHEN `address_entry`.`address_type`='PHONE'       THEN `address_entry`.`value` ELSE '' END AS `phone`
         ,CASE WHEN `address_entry`.`address_type`='POSTAL_CODE' THEN `address_entry`.`value` ELSE '' END AS `postal_code`
    FROM
        `address_entry`) AS `tmp_entry`
GROUP BY
    `tmp_entry`.`address_id`
;

+------------+-----------+--------------------+-----------+-------+-------+------------+--------------------------+--------------+-------------+
| address_id | country   | street             | city      | state | zip   | prefecture | building                 | phone        | postal_code |
+------------+-----------+--------------------+-----------+-------+-------+------------+--------------------------+--------------+-------------+
|          1 | USA       | One Apple Park Way | Cupertino | CA    | 95014 |            |                          |              |             |
|          2 | 日本国    | 銀座3-5-12         | 中央区    |       |       | 東京都     | サヱグサビル本館         | 03-5159-8200 | 104-0061    |
+------------+-----------+--------------------+-----------+-------+-------+------------+--------------------------+--------------+-------------+
2 rows in set (0.004 sec)

要点としては、失敗例で分かるとおりGROUP BYする場合は何らかの集計関数を使わないと結果が得られらない。

数値項目の場合COUNTやMAXを使った例が多いが、文字列の集計関数は少ない。GROUP_CONCATは数少ない文字列の集計関数である。

GROUP_CONCATの本来の用途はカンマ区切りで列の値を連結した文字列を得ることだが、区切り文字が変更可能なので空文字列を区切り文字にして連結すれば今回の例で上記の結果が得られる。

GitHubで編集を提案

Discussion