0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

[ORACLE][SQL]複数レコードの値を1レコードにまとめるXMLAGG関数の例

Last updated at Posted at 2020-10-22

#XMLAGG関数
・複数レコードの値を1レコードにまとめる、LISTAGG関数とXMLAGG関数があります。
・LISTAGG関数を用いる際に4000バイト文字を超える恐れがある場合は、XMLAGG関数を使用。
・ポイントは、下記SQLの「ORDER BY COL23」です。 綺麗に連結できます。

SELECT COL1,
RTRIM(XMLAGG(XMLELEMENT(e,COL23,'|').EXTRACT('//text()') ORDER BY COL23 ).GetClobVal(),'|') AS COL23
FROM
(
SELECT 
DISTINCT
TBL1.COL1
, TO_CHAR(TBL2.COL3, '99999') ||  '(' || TBL2.COL3 || ')' AS COL23
FROM
TBL1
 LEFT JOIN TBL2 ON TBL1.COL1 = TBL2.COL1
)
GROUP BY COL1
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?