行方向データから1カラムへの変換をcase文の中で実現するSQL Server用クエリー

業務系システムのデータベースで、行方向に並んでいるデータを1カラムの文字列として取得したい場合がたまにある。しかも、それをcase文の中で実行できれば嬉しいことがある。case文の中にfetch nextなどは書けないので、あらかじめ本文実行前に別のクエリーでfetch nextで回して取得しておく方法もあるが、どうもスマートではない。

いろいろ探してみたらfor xml句とstuff関数を活用する方法が書かれていた。

[SQL Server Developer Center SQL Server Forums] T-SQL Equivalent For Group_Concat() Function

例題として以下にテーブルを挙げ、実際にクエリーを書いてみる。

Groupテーブルに、とあるグループのデータがあり、Individualテーブルにグループに所属する個別データがあるとする。IndividualテーブルのGroupIDは、グループに所属していれば所属するグループのIDが格納されており、所属していなければ0が格納されているとする。

クエリーの結果としてGroupNameとIndividualNameの複合データが欲しいが、グループに所属する個別データはGroupNameの後ろに括弧で入れて表示したいとする。

このクエリーを実行すると、グループに所属していない個別データはそのまま表示され、グループに所属しているデータはグループ名の後ろに括弧付きで出力される。グループの一覧と個別の一覧はunion句を使って統合している。

括弧内の個別データはfor xml句を利用している。for xml句は結果をXML形式にして返してくれる。例えば、次のクエリーを実行すると、IndividualNameタグが付与された結果が返ってくる。

このクエリーのselect文のIndividualNameカラムの前に「’,’」を入れると名前のついていないカラムになりタグが消える。

このデータの最初の「,」は不要なのでSTUFF関数で除去してあげるという仕組みだ。

少々ややこしいが、このテクニックを覚えておけば、例えばグループ化されたデータの一覧取得などでグループ内のデータがどうなっているか見たいという要望があった場合に、一覧を表示するプログラム側で変更を行わなくてもグループ名のカラムに中身のデータを表示することができる。

ちなみにグループ内のデータのいくつかを見たいという場合には、STUFF関数内のselect文にtop句をつけて件数を指定してあげればよい。

グループ内の取得件数は3件ぐらいが妥当だと思うが、例題なのでここでは1件としている。ちなみに1件の場合はfor xml句で一覧を1カラムで取得して、STUFF関数で先頭の「,」を除去する必要はなく、そのままselect top 1でOKである。