PSI Labs RSS feed

[SQL]行(レコード)から列(カラム)への変換処理(LISTAGG関数)

SQLでの行から列への変換または列から行への変換は、具体的手段から正規化についてのあるべき論など多岐に渡って
よく話題に上がる問題ですが、ここでは 行から列への変換の方についての方法を紹介します。

----------------------------------------------

ここでは Oracle11g R2で追加された LISTAGG関数を使用する方法を紹介します。
(※ MySQLではGROUP_CONCAT 関数でほぼ同じことが可能です。)

このような社員名と担当地区を持つテーブルがあるとします。
1名の社員は複数の地区を担当します。

これを下記のように社員ごとにカンマ区切りで1項目に纏める必要があるとします。

社員名  担当地区(まとめ)
----------------------------------------------------------------------
社員1  地区A,地区C,地区D,地区E,地区X
社員2  地区A,地区B

この場合、LISTAGG関数を下記のように使用して下さい。

select
  SYAIN_NAME   AS "社員名"
 ,LISTAGG(TANTO_CHIKU , ',') WITHIN GROUP (order by TANTO_CHIKU) 
               AS "担当地区(まとめ)"
from TEST1
group by SYAIN_NAME

 

【実行結果】

セパレーター(ここではカンマ)を省略すると区切り文字無しで文字が繋がります。
WITHN GROUP の後のカッコ内の order by の指定により連結順を設定することが出来ます。

詳細な関数の仕様は下記のOracleのマニュアルのHPを参照下さい。
http://docs.oracle.com/cd/E16338_01/server.112/b56299/functions089.htm

****************************************************************************************

・おまけ

ちなみに私は上記のようにカンマ区切りの値をJavaのクラスに設定した後、
それをList型に変換して返すgetter使って、下記のように画面表示を行っています。

【SQLで取得した値を設定するクラス】

public class SyainData {

    private String syain_name;   //  社員名
    private String tanto_chiku;   // カンマ区切りの担当地区

    (中略)

    // カンマ区切り担当地区をListにして返す
    public List<String> getListTantoChiku() {
           String[] chikuStr = chiku_name.split(",");
           List chikuList = Arrays.asList(chikuStr);
           return chikuList;
    }

}

【JSPの担当地区の表示処理部分】

<tr>
(中略)
    <td>
        <logic:notEmpty name="syainData" property="chiku_name" >
            <nested:iterate id="dispVal" property="listTantoChiku" indexId="col_index">
                <% if(col_index.intValue() > 0){ %>
                    <br />
                <% } %>
                <bean:write  name="dispVal" />
            </nested:iterate>
        </logic:notEmpty>
    </td>
</tr>

【表示される画面】

このようにすることで1レコード1行というシンプルな構造にしたまま、
列の中に複数項目を設定することが出来るようになりました。