22127


1日に4時限あり，複数クラスの割当表を作成しています。  
最低1時限から受講者は参加し，最大4時限で，どの組にも参加できます。  
もちろん参加しないで空くこともあります。

DBには左側のようにレコードが保存されていて，それを行列変換したいのですが，行き詰っています。

どうぞよろしくお願いします。

[![左がDB格納状態のテーブル。右側が出力させたい形式](https://i.stack.imgur.com/i3KGE.png)](https://i.stack.imgur.com/i3KGE.png)


CC BY-SA 3.0
2016-02-15T10:08:44.073
22106
2016-02-17T07:58:47.260
14411
question
mysql sql
MySQLの行列変換で複数条件指定


例示のテーブルを単純に行列変換だけしました。  
時限ごとにぶった切った4つの副問い合わせ結果から組と名前を拾い上げる感じ。

※もとのテーブルをbasetblとしています  
※正しく動作するか試していません。何も考えずゴリゴリ書いただけです  
※「参加しないで空くこともある」の条件を無視しています

[code]

    select distinct
    c.組,
    j1.名前 AS '1時限',
    j2.名前 AS '2時限',
    j3.名前 AS '3時限',
    j4.名前 AS '4時限'
    from
    ( select distinct 組 from basetbl) c,
    ( select 組,名前 from basetbl where 時限 = '1時限') j1,
    ( select 組,名前 from basetbl where 時限 = '2時限') j2,
    ( select 組,名前 from basetbl where 時限 = '3時限') j3,
    ( select 組,名前 from basetbl where 時限 = '4時限') j4
    where
    c.組=j1.組,
    c.組=j2.組,
    c.組=j3.組,
    c.組=j4.組
    order by
    c.組
    
[/code]

SQL1文で全てをやろうとすると性能が犠牲になるので  
select * で全て抽出し、プログラム内で加工するか  
軸を決めて(上記の場合は時限)、軸ごとのviewを作り、そこから抽出したほうが良いです。

上記のSQLもデータ件数が増えるとまず返って来ないでしょうね


CC BY-SA 3.0
2016-02-15T18:07:06.103
22126
2016-02-15T21:03:45.097
2016-02-15T21:03:45.097
7837
14417
22106
answer


一般的にこう言う操作はピボット(PIVOT)と言いますので、そういうキーワードで調べれば答えが見つかります。SQLでなくてもflattenとかflatMapとか、よくある操作です。

ご希望の結果とは若干違うかもしれませんがMySQLであればGROUP_CONCATを使うと、SELECTしてきた文字列をカンマ区切りの文字列に変換できます。SEPARATORを指定すればカンマ以外の文字を指定することもできます。

組をcls,名前をname,時限をperiodとして、テーブルを作成して、実験した結果です。

[code]

    create table t1 (
      cls varchar(1),
      name varchar(1),
      period varchar(1)
    );
    
[/code]

このテーブルにデータを入れて

[code]

    insert into t1 values(1,'a',1), (1,'b',1), (2,'c',1), (2,'d',1), (3,'e',1), (3,'f',1), (4,'g',1), (4,'h',1), (1,'a',2), (1,'c',2), (2,'i',2), (2,'j',2), (3,'k',2), (3,'l',2), (4,'m',2), (4,'b',2), (1,'n',3), (1,'o',3), (2,'p',3), (2,'q',3), (3,'r',3), (3,'s',3), (4,'t',3), (4,'u',3), (1,'v',4), (1,'w',4), (2,'x',4), (2,'a',4), (3,'f',4), (3,'h',4), (4,'y',4), (4,'z',4);
    
[/code]

SELECTを実行します。

[code]

    select cls,
           group_concat(case when period=1 then name end order by 1) as period1,
           group_concat(case when period=2 then name end order by 1) as period2,
           group_concat(case when period=3 then name end order by 1) as period3,
           group_concat(case when period=4 then name end order by 1) as period4
    from t1
    group by cls
    order by cls
    
[/code]

結果

[code]

    cls      period1    period2    period3    period4
    1        a,b        a,c        n,o        v,w
    2        c,d        i,j        p,q        a,x
    3        e,f        k,l        r,s        f,h
    4        g,h        b,m        t,u        y,z
    
[/code]

追記：受講者の名前のソートを追加しました。


CC BY-SA 3.0
2016-02-15T20:34:26.177
22127
2016-02-16T04:43:02.450
2016-02-16T04:43:02.450
7837
7837
22106
answer


名前の並びに指定がなかったので、ひとまずアルファベット順に並べて作ってみました。  
名前の並びに自由度をもたせるには、順序を格納する列を追加してください。

[code]

    CREATE TABLE `参加表` (
      `組` int(11) NOT NULL,
      `名前` varchar(256) NOT NULL,
      `時限` int(11) NOT NULL
    );
    
    INSERT INTO `参加表` (`組`, `名前`, `時限`)
    VALUES (1, 'Aさん', 1)
    , (1, 'Bさん', 1)
    , (2, 'Cさん', 1)
    , (2, 'Dさん', 1)
    , (3, 'Eさん', 1)
    , (3, 'Fさん', 1)
    , (4, 'Gさん', 1)
    , (4, 'Hさん', 1)
    , (1, 'Aさん', 2)
    , (1, 'Cさん', 2)
    , (2, 'Iさん', 2)
    , (2, 'Jさん', 2)
    , (3, 'Kさん', 2)
    , (3, 'Lさん', 2)
    , (4, 'Mさん', 2)
    , (4, 'Bさん', 2)
    , (1, 'Nさん', 3)
    , (1, 'Oさん', 3)
    , (2, 'Pさん', 3)
    , (2, 'Qさん', 3)
    , (3, 'Rさん', 3)
    , (3, 'Sさん', 3)
    , (4, 'Tさん', 3)
    , (4, 'Uさん', 3)
    , (1, 'Vさん', 4)
    , (1, 'Wさん', 4)
    , (2, 'Xさん', 4)
    , (2, 'Aさん', 4)
    , (3, 'Fさん', 4)
    , (3, 'Hさん', 4)
    , (4, 'Yさん', 4)
    , (4, 'Zさん', 4);
    
[/code]

たとえば、4組を取得するには下記のSQLを実行します。([実行結果](http://sqlfiddle.com/#!9/5d47b/1/5 "SQL
Fiddle"))  
全部の組を取得するには `UNION ALL` で結合するか、PHP側でループを回して組み立ててください。

[code]

    SET @rownum = 0;
    SET @rownum1 = 0;
    SET @rownum2 = 0;
    SET @rownum3 = 0;
    SET @rownum4 = 0;
    SELECT rownum_base.`組` "組"
    , period1.`名前` "1時限"
    , period2.`名前` "2時限"
    , period3.`名前` "3時限"
    , period4.`名前` "4時限"
    FROM
    ( SELECT @rownum := @rownum + 1 rownum
      , `組`
      FROM `参加表`
      WHERE `組` = 4
      ORDER BY `名前`
    ) rownum_base
      LEFT JOIN
    ( SELECT @rownum1 := @rownum1 + 1 rownum
      , `参加表`.*
      FROM `参加表`
      WHERE `組` = 4
        AND `時限` = 1
      ORDER BY `名前`
    ) period1
      ON period1.rownum = rownum_base.rownum
      LEFT JOIN
    ( SELECT @rownum2 := @rownum2 + 1 rownum
      , `参加表`.*
      FROM `参加表`
      WHERE `組` = 4
        AND `時限` = 2
      ORDER BY `名前`
    ) period2
      ON period2.rownum = rownum_base.rownum
      LEFT JOIN
    ( SELECT @rownum3 := @rownum3 + 1 rownum
      , `参加表`.*
      FROM `参加表`
      WHERE `組` = 4
        AND `時限` = 3
      ORDER BY `名前`
    ) period3
      ON period3.rownum = rownum_base.rownum
      LEFT JOIN
    ( SELECT @rownum4 := @rownum4 + 1 rownum
      , `参加表`.*
      FROM `参加表`
      WHERE `組` = 4
        AND `時限` = 4
      ORDER BY `名前`
    ) period4
      ON period4.rownum = rownum_base.rownum
    WHERE
      COALESCE(period1.rownum, period2.rownum
    , period3.rownum, period4.rownum) IS NOT NULL
    
[/code]


CC BY-SA 3.0
2016-02-16T03:33:25.793
22146
2016-02-16T07:47:19.110
2016-02-16T07:47:19.110
8431
8431
22106
answer


ついでです。SQL のみで実現するとするとこうなります。([実行結果](http://sqlfiddle.com/#!9/5d47b/8/0 "SQL
Fiddle"))

rownum_base で枠を作り、時限単位に並べた四つの表を結合します。

[code]

    SELECT rownum_base.`組` "組"
    , period1.`名前` "1時限"
    , period2.`名前` "2時限"
    , period3.`名前` "3時限"
    , period4.`名前` "4時限"
    FROM
    ( SELECT DISTINCT
      ( SELECT COUNT(*) FROM `参加表` 
        WHERE `名前` <= A.`名前` AND `組` = A.`組` AND `時限` = A.`時限`
      ) rownum
      , `組`
      FROM `参加表` A
    ) rownum_base
      LEFT JOIN
    ( SELECT
      ( SELECT COUNT(*) FROM `参加表`
        WHERE `名前` <= P1.`名前` AND `組` = P1.`組` AND `時限` = P1.`時限`
      ) rownum
     , P1.*
      FROM `参加表` P1
      WHERE `時限` = 1
    ) period1
      ON period1.rownum = rownum_base.rownum
        AND period1.`組` = rownum_base.`組`
      LEFT JOIN
    ( SELECT
      ( SELECT COUNT(*) FROM `参加表`
        WHERE `名前` <= P2.`名前` AND `組` = P2.`組` AND `時限` = P2.`時限`
      ) rownum
      , P2.*
      FROM `参加表` P2
      WHERE `時限` = 2
    ) period2
      ON period2.rownum = rownum_base.rownum
        AND period2.`組` = rownum_base.`組`
      LEFT JOIN
    ( SELECT
      ( SELECT COUNT(*) FROM `参加表`
        WHERE `名前` <= P3.`名前` AND `組` = P3.`組` AND `時限` = P3.`時限`
      ) rownum
      , P3.*
      FROM `参加表` P3
      WHERE `時限` = 3
    ) period3
      ON period3.rownum = rownum_base.rownum
        AND period3.`組` = rownum_base.`組`
      LEFT JOIN
    ( SELECT
      ( SELECT COUNT(*) FROM `参加表`
        WHERE `名前` <= P4.`名前` AND `組` = P4.`組` AND `時限` = P4.`時限`
      ) rownum
      , P4.*
      FROM `参加表` P4
      WHERE `時限` = 4
    ) period4
      ON period4.rownum = rownum_base.rownum
        AND period4.`組` = rownum_base.`組`
    WHERE
      COALESCE(period1.rownum, period2.rownum
    , period3.rownum, period4.rownum) IS NOT NULL;
    
[/code]


CC BY-SA 3.0
2016-02-16T10:38:03.580
22168
2016-02-16T10:38:03.580
8431
22106
answer