Oracle 10g 多列字符串连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5228242/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle 10g multiple column string concatenation
提问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 day.||a nice||Have B Nice Work. C this work!||do||we can||Yes
So basically the Desc column for output table is a concatenation of the SEQ values from Table B and the values are appended in the descending order of the SEQ and delimited by || ?
所以基本上输出表的 Desc 列是表 B 中 SEQ 值的串联，这些值按 SEQ 的降序附加，并由 || 分隔 ?
Any help with the SQL?
对 SQL 有帮助吗？
FYI - Looking for solution without using functions or stored procedures
仅供参考 - 寻找不使用函数或存储过程的解决方案
采纳答案by Dave Costa
A hierarchical query should work. A little extra trickery is needed since you want to start with the highest SEQ for each PID.
分层查询应该可以工作。由于您希望从每个 PID 的最高 SEQ 开始，因此需要一些额外的技巧。
SELECT pid, fulldesc FROM (
SELECT pid, SYS_CONNECT_BY_PATH( desc, '||' ) fulldesc, seq, minseq FROM (
SELECT pid, seq, desc,
MAX(seq) OVER (PARTITION BY pid) maxseq,
MIN(seq) OVER (PARTITION BY pid) minseq
FROM tableB
)
START WITH seq = maxseq
CONNECT BY pid = PRIOR pid AND seq = PRIOR seq - 1
)
WHERE seq = minseq
ORDER BY pid
;
Edit:One way to add a filter as requested in comment:
编辑：根据评论中的要求添加过滤器的一种方法：
SELECT pid, fulldesc FROM (
SELECT pid, SYS_CONNECT_BY_PATH( desc, '||' ) fulldesc, seq, minseq FROM (
SELECT pid, seq, desc,
MAX(seq) OVER (PARTITION BY pid) maxseq,
MIN(seq) OVER (PARTITION BY pid) minseq
FROM tableB
WHERE pid IN (SELECT pid FROM tableB WHERE desc='day.')
)
START WITH seq = maxseq
CONNECT BY pid = PRIOR pid AND seq = PRIOR seq - 1
)
WHERE seq = minseq
ORDER BY pid
回答by Gary Myers
From here
从这里
But I'd go with a function any day.
但我随时都会去参加一个函数。
SQL> select deptno
2 , rtrim(ename,',') enames
3 from ( select deptno
4 , ename
5 , rn
6 from emp
7 model
8 partition by (deptno)
9 dimension by (row_number() over
10 (partition by deptno order by ename) rn
11 )
12 measures (cast(ename as varchar2(40)) ename)
13 rules
14 ( ename[any] order by rn desc = ename[cv()]||','||ename[cv()+1]
15 )
16 )
17 where rn = 1
18 order by deptno
19 /
DEPTNO ENAMES
---------- ----------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
回答by a_horse_with_no_name
Here are a lot of examples on how to do it (some of them have been mentioned already) including a complete implementation of something similar to listagg():
这里有很多关于如何做的例子（其中一些已经提到过），包括类似于 listagg() 的东西的完整实现：
回答by Koshinae
You want to do something in Oracle what GROUP_CONCATdoes in MySQL?
你想在 Oracle 中做些什么GROUP_CONCAT在 MySQL 中做什么？
You can use WM_CONCATif it is present:
http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php#wm_concatBut it is undocumented, so I wouldn't use that on production, if I were you.
WM_CONCAT如果它存在，您可以使用：http:
//www.oracle-base.com/articles/misc/string-aggregation-techniques.php#wm_concat但它没有记录，所以我不会在生产中使用它，如果我是你吗。
On 10g there is no LISTAGGyet, unfortunately.
LISTAGG不幸的是，在 10g 上还没有。
For production environments on 10g, I would go with Dave Costa's answer.
对于 10g 的生产环境，我会同意 Dave Costa 的回答。
