MySQL (MariaDB) で行と列を入れ替える
結論
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
の本来の用途はカンマ区切りで列の値を連結した文字列を得ることだが、区切り文字が変更可能なので空文字列を区切り文字にして連結すれば今回の例で上記の結果が得られる。
Author And Source
この問題について(MySQL (MariaDB) で行と列を入れ替える), 我々は、より多くの情報をここで見つけました https://zenn.dev/eyasuyuki/articles/8d233522db48bb9e4813著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Collection and Share based on the CC protocol