質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.04%

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

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,728

umikaze21

score 10

前提・実現したいこと

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 row1key as row2a AS val FROM テーブルデータ
union
SELECT CONCAT( "b" ) AS row1key as row2b AS val FROM テーブルデータ 
union
SELECT CONCAT( "c" ) AS row1key as row2c AS val FROM テーブルデータ

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

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

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • te2ji

    2016/07/24 11:00

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

    キャンセル

  • umikaze21

    2016/07/24 11:21

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

    キャンセル

回答 3

+3

これは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;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/25 22:04

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

    キャンセル

checkベストアンサー

+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化するとカラム追加があっても、
ストアド側の変更は不要となりますしね^^

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/25 22:16

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

    キャンセル

+2

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

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とやると取得できます。

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

追記

フィールド名直書きでいいなら、

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;

でいいね。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/25 22:22

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

    キャンセル

  • 2016/07/25 22:25

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.04%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる