oracleSQLで複数行を1行で取得したい

Question

前提・実現したいこと

取得した複数行を１行で取得したいです。
お知恵をお貸しください。

cdをキーとしているテーブルです。

cd sub_cd
A1 111,222,333
A2 222,444

sub_cd LIKE '%222%'で検索して、
該当する行のcdとsub_cdをカンマ区切りで１つの項目として取得したいです。

上の場合で「222」で検索したとき、「A1,111,222,333,A2,444」
という値を返してもらいたいです。（重複は削除できればなおうれしい。）

データの持ち方を見直すべきかの瀬戸際です。
ご教示よろしくお願いいたします。

-------------追記
追記すべき情報のご指摘ありがとうございます。
oracleのバージョンは11です。

取得した返却結果を、別のアプリケーションにて使用します。
アプリケーション側で、
cdとsub_cdを同じ項目として扱う設定をしており、
取得結果を受け渡すと、カンマ区切りのデータをor検索する機能で、
検索できる想定です。

そちらのアプリケーションの制限で、
おかしな結果を必要としている状況です…
よろしくお願いいたします。

Comments

[sazi]

「A1,111,222,333,A2,444」こんな風な返却内容の識別は可能なんですか？

[n_takapyon]

Oracleのバージョン次第で対応も変わると思いますので環境についても記述された方が良いかと思います。

Answer 1

oracle 11g以降であれば、listagg()が使用できます。

select listagg(cd || ',' || sub_cd, ',') within group(order by cd)
from tbl
where sub_cd like '%222%'

追記

いったんバラして、たたみ直す構成にしてみました。
要件が分かりませんが、必要に応じて加工すれば、目的は果たすはず。

with
  tbl_expand as (-- sub_cdを展開。その際に重複が除外される。
    select distinct cd, trim(regexp_substr(sub_cd, '[^,]+', 1, level)) sub_cd
    from   tbl
    connect by instr(sub_cd, ',', 1, level - 1) > 0
  )
, tbl_contract as (-- 展開したものを再度折りたたむ
    select cd, listagg(sub_cd, ',') within group(order by sub_cd) sub_cd
    from tbl_expand
    group by cd
  )
select listagg(cd || ',' || sub_cd, ',') within group(order by cd)
from tbl_contract
where sub_cd like '%222%'

#データ

CREATE TABLE tbl
    (cd varchar2(2), sub_cd varchar2(11))
;
INSERT ALL 
    INTO tbl (cd, sub_cd)
         VALUES ('A1', '111,222,333')
    INTO tbl (cd, sub_cd)
         VALUES ('A2', '222,444')
    INTO tbl (cd, sub_cd)
         VALUES ('A3', '222')
SELECT * FROM dual
;

#tbl_expand

CD	SUB_CD
A1	111
A1	222
A1	333
A2	222
A2	444
A3	222

#tbl_contract

CD	SUB_CD
A1	111,222,333
A2	222,444
A3	222

Comments

[ram112]

ご回答ありがとうございます。
無事取得できました。

sub_cdが重複する可能性があり、重複削除をしたく、
少し調べたのですが、listagg()内でのdistinctはできないようですね…！

なにか別の方法でdistinctする方法はありますでしょうか？

[sazi]

group byかdistinct で重複を排除したものを元にすれば良いかと思いますが。

[sazi]

ん？sub_cd内の値に重複が有るってことですか？

[ram112]

いえ、１レコードのsub_cd内には重複がないのですが、

cd sub_cd
A1 111,222,333
A2 222,444
A3 222

試しているのですが、
必ずしもsub_cdの中の形が同じではないので、
distinctでうまく省くことができていない状態です。

さらに、考えていてもう一つ気づいたのですが、
検索というスタイルでなく、これを

sub_cd　listagg結果
111 …
222 　…

という形のVIEWにしなければならないことにも気づきました。
SQLにブランクがあり申し訳ないのですが、
上記のような場合、何を使えばいいのか教えて頂きたいです。
よろしくお願いいたします。

[sazi]

>A1 111,222,333
>A2 222,444
>A3 222
上記をどのように整形したいのかが、伝わってきません。
後半のVIEWに関しても同様なので、得たい結果も含めて質問を編集して下さい。
解決済みは一旦外したほうが良いですね。

[Orlofsky]

正規化ができていないからテーブル設計を見直しましょう。通常第3正規形までやります。今のままだとどんどんトリッキーなコードを書かなければならなくなり、システムの維持が重い負担になります。

[ram112]

saziさん

確認が遅くなってしまい申し訳ありません。
追記で更新くださりありがとうございました。

テーブルの正規化をやり直さないで
工数を押さえてできないか、という要望で
正規化せずに対応できる方法をなんとか考案したく…
saziさんのSQLで、ほしい形で出力できました。

今後のことも考え、tbl_expandの形で
正規化をすべきであることもプッシュしてみます。

ありがとうございました。

[sazi]

正規化できるならそれに越したことはないですからね。
正規化の際には、データ移行にtbl_expandのロジックが使えると思います。

Answer 2

気になったのでテーブルを正規化してみました。
saziさんのコードを借用しました。

CREATE TABLE tbl
    (cd varchar2(2), sub_cd varchar2(3))
;
INSERT ALL 
    INTO tbl (cd, sub_cd)
         VALUES ('A1', '111')
    INTO tbl (cd, sub_cd)
         VALUES ('A1', '222')
    INTO tbl (cd, sub_cd)
         VALUES ('A1', '333')
    INTO tbl (cd, sub_cd)
         VALUES ('A2', '222')
    INTO tbl (cd, sub_cd)
         VALUES ('A2', '444')
    INTO tbl (cd, sub_cd)
         VALUES ('A3', '222')
SELECT * FROM dual
;
COMMIT ;

select listagg(B.cd || ',' || B.sub_cd, ',') within group(order by B.cd) AS sub_cd  -- cd と sub_cd を1行にまとめる
FROM(
    select A.cd, listagg(A.sub_cd, ',') within group(order by A.sub_cd) AS sub_cd  -- 同一cd の sub_cd を1行にまとめる
    FROM(
        SELECT ROW_NUMBER() OVER(PARTITION BY sub_cd ORDER BY cd) AS RN
             , cd
             , sub_cd
        from tbl
        ) A
    where A.RN = 1  -- 重複する sub_cd を除く
    GROUP BY A.cd
    ORDER BY A.cd
    ) B ;

SUB_CD
---------------------
A1,111,222,333,A2,444

もっとシンプルになるかと思っていたんですが、最終的に横に1行で並べるのが面倒ですね。

パフォーマンス・チューニングに呼ばれると正規化がきちんとできていると10時間かかっていた処理を1時間にできることもありますし、正規化がズタズタだとテーブルをきちんと正規化しないとまともにチューニングできず、正規化すると莫大な費用がかかるのでほとんど成果をだせないこともあります。

Comments

[ram112]

回答ありがとうございました。
助言いただいた正規化の必要性についても
改めて認識し、正規化も視野に入れて進めます。