質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.48%

  • SQL

    2480questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • Oracle Database 11g

    197questions

    Oracle DatabaseはRDBMSの商品です。具体的な発売商品として知られているのが、 Oracle9i、Oracle10g、Oracle 11gとOracle 12cです。

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 235

ram112

score 1

 前提・実現したいこと

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

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

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

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

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

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

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

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • sazi

    2018/06/13 17:21 編集

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

    キャンセル

  • n_takapyon

    2018/06/13 17:22

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

    キャンセル

回答 2

checkベストアンサー

+3

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/13 17:43

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

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

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

    キャンセル

  • 2018/06/13 17:51

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

    キャンセル

  • 2018/06/13 18:14

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

    キャンセル

  • 2018/06/13 18:32

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

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

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

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

    sub_cd listagg結果
    111 …
    222  …

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

    キャンセル

  • 2018/06/13 18:37

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

    キャンセル

  • 2018/06/13 23:09

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

    キャンセル

  • 2018/06/14 18:53

    saziさん

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

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

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

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

    キャンセル

  • 2018/06/14 19:36

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

    キャンセル

0

気になったのでテーブルを正規化してみました。
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時間にできることもありますし、正規化がズタズタだとテーブルをきちんと正規化しないとまともにチューニングできず、正規化すると莫大な費用がかかるのでほとんど成果をだせないこともあります。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/14 18:58

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

    キャンセル

関連した質問

同じタグがついた質問を見る

  • SQL

    2480questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • Oracle Database 11g

    197questions

    Oracle DatabaseはRDBMSの商品です。具体的な発売商品として知られているのが、 Oracle9i、Oracle10g、Oracle 11gとOracle 12cです。