MYSQL でクロス集計の逆のようなことをしたい

Question

###前提・実現したいこと
Mysql 5.5.36 にて テーブルデータから出力結果のようなテーブルを得たいのですが
どのようなSQL文を書けばよろしいでしょうか？

テーブルデータ
key a b c

あ 11 12 13
い 21 22 23
う 31 32 33

出力結果
row1 row2 val

a あ 11
a い 21
a う 31
b あ 12
b い 22
b う 32
c あ 13
c い 23
c う 33

###試したこと
SELECT CONCAT( "a" ) AS row1, key as row2, a AS val FROM テーブルデータ
union
SELECT CONCAT( "b" ) AS row1, key as row2, b AS val FROM テーブルデータ
union
SELECT CONCAT( "c" ) AS row1, key as row2, c AS val FROM テーブルデータ

上記のようなSQL文で一応できますが、実際にはカラム数が多いのでしんどいです。
カラム数が増えてもSQL分は変わらないようにできないものでしょうか？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME != "Key" and TABLE_NAME = 'テーブルデータ' order by TABLE_NAME

のようにカラム名を取得する分を絡めればよさそうですがその先は検討が付きません。

助言などいただければ幸いです。

Comments

[退会済みユーザー]

row1 row2 val の値、間違ってませんか？

[umikaze21]

テーブル2の出力結果のほうが間違っていました。
また、SQL文のテーブル名も本文と微妙に違っていたので訂正しました。
ご指摘ありがとうございました。

Answer 1

これはa,b,cをデータとして渡せば簡単なロジックです

create table tbl(`key` varchar(10),A int,B int,C int);
insert into tbl values('あ',11,12,13),('い',21,22,23),('う',31,32,33);
create table tmp(row1 varchar(10));
insert into tmp values('a'),('b'),('c');

表示

select row1,`key` as row2,case 
when row1='a' then A 
when row1='b' then B 
when row1='c' then C 
end as value
from tbl,tmp
order by row1,value;

tmpテーブルはテンポラリテーブルとして作っても構いません

追記

ピボットテーブル的なアプローチをすると書き方がすっきりするかも

create table pivot(row1 varchar(10),pA int null,pB int null,pC int null);
insert into pivot values('a',1,null,null),('b',null,1,null),('c',null,null,1);

表示

select row1,`key` as row2,coalesce(A*pA,B*pB,C*pC) as value
from tbl,pivot;

Comments

[umikaze21]

回答ありがとうございました。
最初のテーブルとそのテーブルを管理するためのテーブルとSQL文の3つの同期をとるのが少し不安に思えました。しかし、そのアルゴリズムというか構造はすごく勉強になりました。また、Mysqlの勉強を始めて間もないのでcoalesce自体初めて知ったのでもうちょっと基礎から勉強していきたいと思います。。。

Answer 2

SQL一本に収めるなら下記のような感じですかねぇ･･･。

SELECT
    B.COLUMN_NAME
    , T.key
    , CASE B.COLUMN_NAME
        WHEN 'a' THEN T.a
        WHEN 'b' THEN T.b
        WHEN 'c' THEN T.c
        ELSE NULL 
      END AS VAL
FROM
    テーブルデータ T 
    CROSS JOIN ( 
        SELECT
            COLUMN_NAME 
        FROM
            INFORMATION_SCHEMA.COLUMNS 
        WHERE
            COLUMN_NAME != "key" 
            AND TABLE_NAME = 'テーブルデータ' 
    ) B 
ORDER BY
    B.COLUMN_NAME
    , T.key

ただCROSS JOINを行っている都合上パフォーマンス面に不安はあるのと、
カラム追加があると結局微々たる修正は必要です。

上記が許容できない場合はshi_ueさんの回答のようにストアド化するのがベストかと思います。

ストアドの内部で動的SQL化するとカラム追加があっても、
ストアド側の変更は不要となりますしね＾＾

Comments

[umikaze21]

回答ありがとうございます。
今回は私のような初心者が趣味でやっていることもあり、あまりレコード数は大きくならない見込みなので、性能より理解のしやすさ重視でこちらをベストアンサーにさせていただきたいと思います。大変たすかりました！

Answer 3

自分の学習がてら、ストアドプロシージャーを書いてみました。
カーソルというものを使っています。

DELIMITER //
CREATE PROCEDURE demo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE field VARCHAR(20);
  DECLARE cur CURSOR FOR SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME != 'key' and TABLE_NAME = 'テーブル名' order by COLUMN_NAME;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cur;
  SET @STMT = '';
  read_loop: LOOP
    FETCH cur INTO field;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF @STMT != '' THEN
      SET @STMT = CONCAT(@STMT,' UNION ');
    END IF;
    SET @STMT = CONCAT(@STMT, 'SELECT "',field,'" AS row1, `key` AS row2,',field,' AS val FROM テーブル名'); 
  END LOOP;
  CLOSE cur;

  PREPARE stmt FROM @STMT;
  EXECUTE stmt;

END
//
DELIMITER ;
```要するにはフィールドの一覧を取得し、UNIONクエリSQLを生成→取得する、ってだけです。
`CALL demo`とやると取得できます。

もっといいやり方があるかもしれませんが・・・

追記
---
フィールド名直書きでいいなら、
```SQL
SELECT
  F.row1,
  T.`key` AS row2,
  CASE F.row1
    WHEN 'a' THEN T.A
    WHEN 'b' THEN T.B 
    WHEN 'c' THEN T.C 
  END AS val
FROM tbl AS T,
 (SELECT COLUMN_NAME AS row1
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE COLUMN_NAME != 'key' AND TABLE_NAME = 'tbl') AS F
ORDER BY row1,row2;
```でいいね。

Comments

[shi_ue]

なんだ、
> カラム数が増えてもSQL分は変わらないようにできないものでしょうか？
って書いてあるのにBAがカラム名直書きとは・・・

[umikaze21]

回答ありがとうございます。
しかしながら、ストアドプロシージャーは私にはハードルが高すぎました（泣　しかし、このようなアプローチもあるということで、とても貴重なとっかかりを頂いたとは思います。これから少しづつでも勉強していきたいと思います。