用于连接 Oracle 中多行列值的 SQL 查询

Question

提问by jagamot

Would it be possible to construct SQL to concatenate column values from multiple rows?

是否可以构造 SQL 来连接多行的列值？

The following is an example:

下面是一个例子：

Table A

表A

PID
A
B
C

Table B

表B

PID   SEQ    Desc

A     1      Have
A     2      a nice
A     3      day.
B     1      Nice Work.
C     1      Yes
C     2      we can 
C     3      do 
C     4      this work!

Output of the SQL should be -

SQL 的输出应该是 -

PID   Desc
A     Have a nice day.
B     Nice Work.
C     Yes we can do this work!

So basically the Desc column for out put table is a concatenation of the SEQ values from Table B?

所以基本上输出表的 Desc 列是表 B 中 SEQ 值的串联？

Any help with the SQL?

对 SQL 有帮助吗？

Answer 1

回答by Lou Franco

There are a few ways depending on what version you have - see the oracle documentation on string aggregation techniques. A very common one is to use LISTAGG:

有几种方法取决于您拥有的版本 - 请参阅有关字符串聚合技术的oracle 文档。一个非常常见的方法是使用LISTAGG：

SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;

Then join to Ato pick out the pidsyou want.

然后加入到A挑选pids你想要的。

Note:Out of the box, LISTAGGonly works correctly with VARCHAR2columns.

注意：开箱即用，LISTAGG仅适用于VARCHAR2列。

Answer 2

回答by Peter

There's also an XMLAGGfunction, which works on versions prior to 11.2. Because WM_CONCATis undocumented and unsupported by Oracle, it's recommended not to use it in production system.

还有一个XMLAGG功能，适用于 11.2 之前的版本。因为WM_CONCAT是无证和不支持的Oracle，建议不要在生产系统中使用它。

With XMLAGGyou can do the following:

有了XMLAGG你可以做到以下几点：

SELECT XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()') "Result" 
FROM employee_names

What this does is

这是做什么的

put the values of the enamecolumn (concatenated with a comma) from the employee_namestable in an xml element (with tag E)
extract the text of this
aggregate the xml (concatenate it)
call the resulting column "Result"

将表中ename列的值（用逗号连接）employee_names放在 xml 元素中（带有标签 E）
提取此文本
聚合xml（连接它）
调用结果列“结果”

Answer 3

回答by Rob van Wijk

With SQL model clause:

使用 SQL 模型子句：

SQL> select pid
  2       , ltrim(sentence) sentence
  3    from ( select pid
  4                , seq
  5                , sentence
  6             from b
  7            model
  8                  partition by (pid)
  9                  dimension by (seq)
 10                  measures (descr,cast(null as varchar2(100)) as sentence)
 11                  ( sentence[any] order by seq desc
 12                    = descr[cv()] || ' ' || sentence[cv()+1]
 13                  )
 14         )
 15   where seq = 1
 16  /

P SENTENCE
- ---------------------------------------------------------------------------
A Have a nice day
B Nice Work.
C Yes we can do this work!

3 rows selected.

I wrote about this here. And if you follow the link to the OTN-thread you will find some more, including a performance comparison.

我在这里写了这个。如果您点击指向 OTN 主题的链接，您会发现更多内容，包括性能比较。

Answer 4

回答by Ashish J

The LISTAGGanalytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. If you are using 11g Release 2 you should use this function for string aggregation. Please refer below url for more information about string concatenation.

该LISTAGG分析功能在推出的Oracle 11g第2版，使它很容易聚集字符串。如果您使用的是 11g 第 2 版，则应使用此函数进行字符串聚合。有关字符串连接的更多信息，请参阅下面的 url。

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

String Concatenation

字符串连接

Answer 5

回答by Kaushik Nayak

As most of the answers suggest, LISTAGGis the obvious option. However, one annoying aspect with LISTAGGis that if the total length of concatenated string exceeds 4000 characters( limit for VARCHAR2in SQL ), the below error is thrown, which is difficult to manage in Oracle versions upto 12.1

正如大多数答案所暗示的那样，LISTAGG是显而易见的选择。然而，一个令人讨厌的方面LISTAGG是，如果连接字符串的总长度超过 4000 个字符（VARCHAR2SQL 中的限制），则会引发以下错误，这在 Oracle 12.1 版本之前很难管理

ORA-01489: result of string concatenation is too long

ORA-01489: 字符串连接的结果太长

A new feature added in 12cR2 is the ON OVERFLOWclause of LISTAGG. The query including this clause would look like:

在12cR2添加了一个新功能是ON OVERFLOW的条款LISTAGG。包含此子句的查询如下所示：

SELECT pid, LISTAGG(Desc, ' ' on overflow truncate) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;

The above will restrict the output to 4000 characters but will not throw the ORA-01489error.

以上将输出限制为 4000 个字符，但不会抛出 ORA-01489错误。

These are some of the additional options of ON OVERFLOWclause:

这些是ON OVERFLOW子句的一些附加选项：

ON OVERFLOW TRUNCATE 'Contd..': This will display 'Contd..'at the end of string (Default is ...)
ON OVERFLOW TRUNCATE '': This will display the 4000 characters without any terminating string.
ON OVERFLOW TRUNCATE WITH COUNT: This will display the total number of characters at the end after the terminating characters. Eg:- '...(5512)'
ON OVERFLOW ERROR: If you expect the LISTAGGto fail with the ORA-01489error ( Which is default anyway ).

ON OVERFLOW TRUNCATE 'Contd..': 这将显示'Contd..'在字符串的末尾（默认为...）
ON OVERFLOW TRUNCATE '': 这将显示 4000 个字符而没有任何终止字符串。
ON OVERFLOW TRUNCATE WITH COUNT: 这将显示终止字符后的总字符数。例如：- ' ...(5512)'
ON OVERFLOW ERROR：如果您希望LISTAGG失败并出现 ORA-01489错误（无论如何都是默认值）。

Answer 6

回答by JonathanDavidArndt

For those who must solve this problem using Oracle 9i (or earlier), you will probably need to use SYS_CONNECT_BY_PATH, since LISTAGG is not available.

对于必须使用 Oracle 9i（或更早版本）解决此问题的人，您可能需要使用 SYS_CONNECT_BY_PATH，因为 LISTAGG 不可用。

To answer the OP, the following query will display the PID from Table A and concatenate all the DESC columns from Table B:

为了回答 OP，以下查询将显示表 A 中的 PID 并连接表 B 中的所有 DESC 列：

SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
       FROM (
              SELECT a.pid, seq, description
              FROM table_a a, table_b b
              WHERE a.pid = b.pid(+)
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;

There may also be instances where keys and values are all contained in one table. The following query can be used where there is no Table A, and only Table B exists:

也可能存在键和值都包含在一张表中的情况。在没有表 A 且仅存在表 B 的情况下，可以使用以下查询：

SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
       FROM (
              SELECT pid, seq, description
              FROM table_b
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;

All values can be reordered as desired. Individual concatenated descriptions can be reordered in the PARTITION BY clause, and the list of PIDs can be reordered in the final ORDER BY clause.

所有值都可以根据需要重新排序。单个串联描述可以在 PARTITION BY 子句中重新排序，PID 列表可以在最后的 ORDER BY 子句中重新排序。

Alternately:there may be times when you want to concatenate all the values from an entire table into one row.

或者：有时您可能希望将整个表中的所有值连接到一行中。

The key idea here is using an artificial value for the group of descriptions to be concatenated.

这里的关键思想是对要连接的描述组使用人工值。

In the following query, the constant string '1' is used, but any value will work:

在以下查询中，使用了常量字符串 '1'，但任何值都可以使用：

SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY unique_id ORDER BY pid, seq) rnum, description
       FROM (
              SELECT '1' unique_id, b.pid, b.seq, b.description
              FROM table_b b
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1;

Individual concatenated descriptions can be reordered in the PARTITION BY clause.

可以在 PARTITION BY 子句中对单个串联描述进行重新排序。

Several other answers on this page have also mentioned this extremely helpful reference: https://oracle-base.com/articles/misc/string-aggregation-techniques

此页面上的其他几个答案也提到了这个非常有用的参考：https: //oracle-base.com/articles/misc/string-aggregation-techniques

Answer 7

回答by Misho

LISTAGG delivers the best performance if sorting is a must(00:00:05.85)
SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description FROM B GROUP BY pid;
COLLECT delivers the best performance if sorting is not needed(00:00:02.90):
SELECT pid, TO_STRING(CAST(COLLECT(Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;
COLLECT with ordering is bit slower(00:00:07.08):
SELECT pid, TO_STRING(CAST(COLLECT(Desc ORDER BY Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;

如果必须进行排序，则 LISTAGG 可提供最佳性能 (00:00:05.85)
SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description FROM B GROUP BY pid;
如果不需要排序 (00:00:02.90)，则 COLLECT 可提供最佳性能：
SELECT pid, TO_STRING(CAST(COLLECT(Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;
带排序的 COLLECT 有点慢（00:00:07.08）：
SELECT pid, TO_STRING(CAST(COLLECT(Desc ORDER BY Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;

All other techniques were slower.

所有其他技术都较慢。

Answer 8

回答by user2865810

Before you run a select query, run this:

在运行选择查询之前，请运行以下命令：

SET SERVEROUT ON SIZE 6000

SELECT XMLAGG(XMLELEMENT(E,SUPLR_SUPLR_ID||',')).EXTRACT('//text()') "SUPPLIER" 
FROM SUPPLIERS;

Answer 9

回答by Krishnakumar MD Amain Infotech

Try this code:

试试这个代码：

 SELECT XMLAGG(XMLELEMENT(E,fieldname||',')).EXTRACT('//text()') "FieldNames"
    FROM FIELD_MASTER
    WHERE FIELD_ID > 10 AND FIELD_AREA != 'NEBRASKA';

Answer 10

回答by user5473005

In the select where you want your concatenation, call a SQL function.

在选择要连接的位置，调用 SQL 函数。

For example:

例如：

select PID, dbo.MyConcat(PID)
   from TableA;

Then for the SQL function:

然后对于 SQL 函数：

Function MyConcat(@PID varchar(10))
returns varchar(1000)
as
begin

declare @x varchar(1000);

select @x = isnull(@x +',', @x, @x +',') + Desc
  from TableB
    where PID = @PID;

return @x;

end

The Function Header syntax might be wrong, but the principle does work.

函数头语法可能是错误的，但原理确实有效。

用于连接 Oracle 中多行列值的 SQL 查询

提问by jagamot

回答by Lou Franco

回答by Peter

回答by Rob van Wijk

回答by Ashish J

回答by Kaushik Nayak

回答by JonathanDavidArndt

回答by Misho

回答by user2865810

回答by Krishnakumar MD Amain Infotech

回答by user5473005

相关推荐

最近更新

标签

用于连接 Oracle 中多行列值的 SQL 查询

提问by jagamot

回答by Lou Franco

回答by Peter

回答by Rob van Wijk

回答by Ashish J

回答by Kaushik Nayak

回答by JonathanDavidArndt

回答by Misho

回答by user2865810

回答by Krishnakumar MD Amain Infotech

回答by user5473005

相关推荐

SQL 比较两个表中的数据

使用 SQL 脚本创建用户、映射到数据库并为该数据库分配角色

SQL 中的 GROUP BY/聚合函数混淆

SQL 如何调用包中的函数

相关推荐

最近更新

标签