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

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

新規登録して質問してみよう
ただいま回答率
85.50%
Oracle Database 11g

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

SQL

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

Q&A

解決済

2回答

5492閲覧

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

ram112

総合スコア9

Oracle Database 11g

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

SQL

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

0グッド

1クリップ

投稿2018/06/13 08:14

編集2018/06/13 08:30

前提・実現したいこと

取得した複数行を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検索する機能で、
検索できる想定です。

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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi

2018/06/13 08:22 編集

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

2018/06/13 08:22

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

回答2

0

ベストアンサー

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

SQL

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

追記

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

SQL

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

#データ

SQL

1CREATE TABLE tbl 2 (cd varchar2(2), sub_cd varchar2(11)) 3; 4INSERT ALL 5 INTO tbl (cd, sub_cd) 6 VALUES ('A1', '111,222,333') 7 INTO tbl (cd, sub_cd) 8 VALUES ('A2', '222,444') 9 INTO tbl (cd, sub_cd) 10 VALUES ('A3', '222') 11SELECT * FROM dual 12;

#tbl_expand

CDSUB_CD
A1111
A1222
A1333
A2222
A2444
A3222

#tbl_contract

CDSUB_CD
A1111,222,333
A2222,444
A3222

投稿2018/06/13 08:30

編集2018/06/13 14:22
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ram112

2018/06/13 08:43

ご回答ありがとうございます。 無事取得できました。 sub_cdが重複する可能性があり、重複削除をしたく、 少し調べたのですが、listagg()内でのdistinctはできないようですね…! なにか別の方法でdistinctする方法はありますでしょうか?
sazi

2018/06/13 08:51

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

2018/06/13 09:14

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

2018/06/13 09: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にブランクがあり申し訳ないのですが、 上記のような場合、何を使えばいいのか教えて頂きたいです。 よろしくお願いいたします。
sazi

2018/06/13 09:37

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

2018/06/13 14:09

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

2018/06/14 09:53

saziさん 確認が遅くなってしまい申し訳ありません。 追記で更新くださりありがとうございました。 テーブルの正規化をやり直さないで 工数を押さえてできないか、という要望で 正規化せずに対応できる方法をなんとか考案したく… saziさんのSQLで、ほしい形で出力できました。 今後のことも考え、tbl_expandの形で 正規化をすべきであることもプッシュしてみます。 ありがとうございました。
sazi

2018/06/14 10:36

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

0

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

SQL

1CREATE TABLE tbl 2 (cd varchar2(2), sub_cd varchar2(3)) 3; 4INSERT ALL 5 INTO tbl (cd, sub_cd) 6 VALUES ('A1', '111') 7 INTO tbl (cd, sub_cd) 8 VALUES ('A1', '222') 9 INTO tbl (cd, sub_cd) 10 VALUES ('A1', '333') 11 INTO tbl (cd, sub_cd) 12 VALUES ('A2', '222') 13 INTO tbl (cd, sub_cd) 14 VALUES ('A2', '444') 15 INTO tbl (cd, sub_cd) 16 VALUES ('A3', '222') 17SELECT * FROM dual 18; 19COMMIT ;

SQL

1select listagg(B.cd || ',' || B.sub_cd, ',') within group(order by B.cd) AS sub_cd -- cd と sub_cd を1行にまとめる 2FROM( 3 select A.cd, listagg(A.sub_cd, ',') within group(order by A.sub_cd) AS sub_cd -- 同一cd の sub_cd を1行にまとめる 4 FROM( 5 SELECT ROW_NUMBER() OVER(PARTITION BY sub_cd ORDER BY cd) AS RN 6 , cd 7 , sub_cd 8 from tbl 9 ) A 10 where A.RN = 1 -- 重複する sub_cd を除く 11 GROUP BY A.cd 12 ORDER BY A.cd 13 ) B ;
SUB_CD --------------------- A1,111,222,333,A2,444

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

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

投稿2018/06/13 18:58

編集2018/06/14 10:49
Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ram112

2018/06/14 09:58

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問