SQL查询可连接Oracle中多个行的列值 [英] SQL Query to concatenate column values from multiple rows in Oracle

问题描述

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

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

以下是示例:

表A

PID
A
B
C

表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!

SQL的输出应为-

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

因此，输出表的Desc列基本上是表B的SEQ值的串联?

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

对SQL有帮助吗?

推荐答案

有几种方法取决于您使用的版本-请参见 LISTAGG :

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:

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

然后加入A以选择所需的pids.

Then join to A to pick out the pids you want.

注意:即开即用，LISTAGG仅适用于VARCHAR2列.

Note: Out of the box, LISTAGG only works correctly with VARCHAR2 columns.

这篇关于SQL查询可连接Oracle中多个行的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！