行方向データから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
例題として以下にテーブルを挙げ、実際にクエリーを書いてみる。
GroupID | GroupName --------|---------- 1 | GroupA 2 | GroupB 3 | GroupC 4 | GroupD 5 | GroupE
GroupID | IndividualID | IndividualName --------|--------------|--------------- 1 | 1 | DataA 2 | 2 | DataB 3 | 3 | DataC 0 | 4 | DataD 5 | 5 | DataE 0 | 6 | DataF 4 | 7 | DataG 1 | 8 | DataH 2 | 9 | DataI 0 | 10 | DataJ 0 | 11 | DataK
Groupテーブルに、とあるグループのデータがあり、Individualテーブルにグループに所属する個別データがあるとする。IndividualテーブルのGroupIDは、グループに所属していれば所属するグループのIDが格納されており、所属していなければ0が格納されているとする。
クエリーの結果としてGroupNameとIndividualNameの複合データが欲しいが、グループに所属する個別データはGroupNameの後ろに括弧で入れて表示したいとする。
select
case (select COUNT(*) from Individual i where i.GroupID = g.GroupID)
when 0 then g.GroupName
else
g.GroupName + '(' +
STUFF((
select ',' + i.IndividualName
from Individual i
where i.GroupID = g.GroupID
order by i.IndividualName
for xml path('')), 1, 1, '')
+ ')'
end DataName
from [Group] g
union
select IndividualName DataName from Individual where GroupID = 0このクエリーを実行すると、グループに所属していない個別データはそのまま表示され、グループに所属しているデータはグループ名の後ろに括弧付きで出力される。グループの一覧と個別の一覧はunion句を使って統合している。
DataName ------------------- DataD DataF DataJ DataK GroupA(DataA,DataH) GroupB(DataB,DataI) GroupC(DataC) GroupD(DataG) GroupE(DataE)
括弧内の個別データはfor xml句を利用している。for xml句は結果をXML形式にして返してくれる。例えば、次のクエリーを実行すると、IndividualNameタグが付与された結果が返ってくる。
select IndividualName from Individual for xml path('')<IndividualName>DataA</IndividualName><IndividualName>DataB</IndividualName><IndividualName>DataC</IndividualName><IndividualName>DataD</IndividualName><IndividualName>DataE</IndividualName><IndividualName>DataF</IndividualName><IndividualName>DataG</IndividualName><IndividualName>DataH</IndividualName><IndividualName>DataI</IndividualName><IndividualName>DataJ</IndividualName><IndividualName>DataK</IndividualName>このクエリーのselect文のIndividualNameカラムの前に「’,’」を入れると名前のついていないカラムになりタグが消える。
select ',' + IndividualName from Individual for xml path(''),DataA,DataB,DataC,DataD,DataE,DataF,DataG,DataH,DataI,DataJ,DataK
このデータの最初の「,」は不要なのでSTUFF関数で除去してあげるという仕組みだ。
少々ややこしいが、このテクニックを覚えておけば、例えばグループ化されたデータの一覧取得などでグループ内のデータがどうなっているか見たいという要望があった場合に、一覧を表示するプログラム側で変更を行わなくてもグループ名のカラムに中身のデータを表示することができる。
ちなみにグループ内のデータのいくつかを見たいという場合には、STUFF関数内のselect文にtop句をつけて件数を指定してあげればよい。
select
case (select COUNT(*) from Individual i where i.GroupID = g.GroupID)
when 0 then g.GroupName
else
g.GroupName + '(' +
STUFF((
select top 1 ',' + i.IndividualName
from Individual i
where i.GroupID = g.GroupID
order by i.IndividualName
for xml path('')), 1, 1, '')
+ '...' + CONVERT(nvarchar(max), (select COUNT(*) from Individual i where i.GroupID = g.GroupID)) + '件)'
end DataName
from [Group] g
union
select IndividualName DataName from Individual where GroupID = 0DataName ------------------- DataD DataF DataJ DataK GroupA(DataA...2件) GroupB(DataB...2件) GroupC(DataC...1件) GroupD(DataG...1件) GroupE(DataE...1件)
グループ内の取得件数は3件ぐらいが妥当だと思うが、例題なのでここでは1件としている。ちなみに1件の場合はfor xml句で一覧を1カラムで取得して、STUFF関数で先頭の「,」を除去する必要はなく、そのままselect top 1でOKである。
select
case (select COUNT(*) from Individual i where i.GroupID = g.GroupID)
when 0 then g.GroupName
else
g.GroupName + '(' +
(select top 1 i.IndividualName
from Individual i
where i.GroupID = g.GroupID
order by i.IndividualName)
+ '...' + CONVERT(nvarchar(max), (select COUNT(*) from Individual i where i.GroupID = g.GroupID)) + '件)'
end DataName
from [Group] g
union
select IndividualName DataName from Individual where GroupID = 0Follow me!
