カンマをデリミタとするレコードを取得したい

質問

お世話になっております。

現在直面している問題に助言を頂けたらと思い質問をさせて頂きました。
もし、ご存じの方がいらっしゃいましたらご回答を宜しくお願い致します。

MySQLにて
テーブルA
カラム１　　カラム２
1 AAA,BBB,CCC　　　　　　
2 BBB,DDD
3 CCC,DDD,EEE,FFF

のようなレコードが入っているとします。
これをカラム２のカンマをデリミタとして分割した文字列でgroup byをしたような結果が欲しいと思っております。

希望結果
文字列　出現回数
AAA 　　1個
BBB 　　2個
CCC 　　2個
DDD　　1個
EEE　　1個

カンマで分けられている文字列の数に規則性はなく、何が入っているかわからない状態、
また、何個の文字列がカンマによって分割されているかわからない状態です。

AA,BB と２つの文字列がカンマ区切りで入っていることもあれば
AA,BBB,CCCC,・・・・　と1000個の文字列がカンマ区切りで入っている状態です。

私が未熟なためか私の調べた限りではこのように取得する方法が見当たりませんした。
DBの構造に関しましては既に動いているサービス内のものですので変更することはできません。

以上、お手隙の際にご回答を宜しくお願い致します。

回答No.1

pringlez · ベストアンサー

そのようなケースなら、普通なら検索結果をプログラミング言語内で処理すると思いますよ。2重のループにすればいいだけなので大して難しくはないでしょう。私ならそうします。

SQLで取得は、不可能ではないかもしれませんが、できたとしても非常に複雑になるのでやめたほうがいいでしょう。

お礼

ご回答ありがとうございます。
１回のSQLで取得することができたらPGのソースを９割再利用できるというメリットがあるため
できるだけSQLにて１回でできたらと思っております。
もしどうしても不可能な場合はPG側にて処理をさせようと思います。

一応オラクルにて１回のSQLで取得することができました。

--元テーブル
CREATE TABLE T_TEST_4 (
KEY VARCHAR2(8),
NAME VARCHAR2(255)
)

--元テーブルのデータ
KEY NAME
------------------------------
1 AA,AAA,BBB,BBBB,ZZZZZ
2 CCC,DDD,EE,FFFF

--SQL
/* SplitSQL */

select
col1,
col2
from(
select
col1,
col2,
row_number() over (partition by col1 order by col2 desc) rnk
from(
select
substr( name,
case rn when 1 then 1
else instr(name, delm, 1, rn - 1) + 1
end
,case rn when 1 then
case instr(name, delm, 1, rn) when 0 then length(name)
else instr(name, delm,1,rn)-1
end
when nvl(length(translate(name, delm || name, delm)), 0) + 1 then length(name)
else instr(name,delm,1,rn)-1-instr(name,delm,1,rn-1)
end
) col1,
key col2
from
T_TEST_4,
(select ',' delm from dual) b,
(select rownum rn from all_catalog) c
where
rn <= nvl(length(translate(name,delm || name, delm)),0) + 1
)
)
where rnk = 1
order by col1;

--SQL実行結果
COL1　　COL2
-------------
AA 1
AAA 1
BBB 1
BBBB 1
CCC 2
DDD 2
EE 2
FFFF 2
ZZZZZ 1