是否有可能构造SQL来连接列值
多行?
以下是一个示例:
表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值的串联?
对SQL有帮助吗?
参见例如:halisway.blogspot.com/2006/08/
请看一下这个解决方案。 这将对您有用。
有几种方法取决于您使用的版本-请参见有关字符串聚合技术的oracle文档。一种很常见的方法是使用LISTAGG:
SELECT pid, LISTAGG(DESC, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;
然后加入A以选择所需的pids。
注意:LISTAGG开箱即用,仅适用于VARCHAR2列。
在Oracle 10g中使用wm_concat()将文本以逗号分隔的序列号的升序连接起来,是否可以使降序用其他东西分隔?
还有一个XMLAGG函数,可用于11.2之前的版本。由于WM_CONCAT没有Oracle的文档记录和支持,因此建议不要在生产系统中使用它。
使用XMLAGG,您可以执行以下操作:
SELECT XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()')"Result"
FROM employee_names
这是什么
将employee_names表中ename列的值(用逗号连接)放在xml元素中(带有标签E)
提取此文本
聚合xml(将其连接)
将结果列称为"结果"
XMLAGG适用于Oracle 12.2。而且,XLMAGG可以连接非常长的字符串,而LISTAGG可能由于它们的最终长度而无法连接。
使用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.
我在这里写过。而且,如果您单击指向OTN线程的链接,则会发现更多内容,包括性能比较。
LISTAGG分析功能是在Oracle 11g第2版中引入的,这使得聚集字符串非常容易。
如果您使用的是11g第2版,则应使用此函数进行字符串聚合。
请参考以下网址,以获取有关字符串连接的更多信息。
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
字符串串联
正如大多数答案所暗示的,LISTAGG是显而易见的选择。但是,LISTAGG的一个令人讨厌的方面是,如果串联字符串的总长度超过4000个字符(SQL中VARCHAR2的限制),则会引发以下错误,这在Oracle 12.1版之前的版本中很难管理。
ORA-01489: result of string concatenation is too long
12cR2中添加的新功能是LISTAGG的ON OVERFLOW子句。
包含此子句的查询如下所示:
SELECT pid, LISTAGG(DESC, ' ' ON overflow TRUNCATE) WITHIN GROUP (ORDER BY seq) AS DESC
FROM B GROUP BY pid;
以上将输出限制为4000个字符,但不会引发ORA-01489错误。
这些是ON OVERFLOW子句的一些其他选项:
ON OVERFLOW TRUNCATE 'Contd..':这将在以下位置显示'Contd..'
字符串的结尾(默认为...)
ON OVERFLOW TRUNCATE '':这将显示4000个字符
没有任何终止字符串。
ON OVERFLOW TRUNCATE WITH COUNT:这将显示总数
终止字符后的末尾字符数。
例如:-'...(5512)'
ON OVERFLOW ERROR:如果您期望LISTAGG失败并显示
ORA-01489错误(仍然是默认值)。
对于必须使用Oracle 9i(或更早版本)解决此问题的用户,您可能需要使用SYS_CONNECT_BY_PATH,因为LISTAGG不可用。
为了回答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;
在某些情况下,键和值都包含在一个表中。在没有表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;
所有值都可以根据需要重新排序。各个串联的描述可以在PARTITION BY子句中重新排序,而PID列表可以在最终的ORDER BY子句中重新排序。
或者:有时您可能希望将整个表中的所有值连接成一行。
这里的关键思想是为要连接的一组描述使用人工值。
在以下查询中,使用常量字符串" 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;
各个串联的描述可以在PARTITION BY子句中重新排序。
此页面上的其他几个答案也提到了这个非常有用的参考:
https://oracle-base.com/articles/misc/string-aggregation-techniques
如果必须进行排序,LISTAGG可提供最佳性能(00:00:05.85)
SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;
如果不需要排序,COLLECT可以提供最佳性能(00:00:02.90):
SELECT pid, TO_STRING(CAST(COLLECT(Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;
按顺序进行收藏会比较慢(00:00:07.08):
SELECT pid, TO_STRING(CAST(COLLECT(Desc ORDER BY Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;
所有其他技术都比较慢。
详细说明您的答案会有所帮助。
John,我不想在文章中重复,但总而言之,这些是以下结果:1.如果必须进行排序,则LISTAGG可以提供最佳性能(00:00:05.85)2.如果不需要进行排序,则COLLECT可以提供最佳性能( 00:00:02.90):SELECT pid,TO_STRING(CAST(COLLECT(Desc)AS varchar2_ntt))AS Vals FROM B GROUP BY pid; 3.按顺序进行收集会比较慢(00:00:07.08):SELECT pid,TO_STRING(CAST(COLLECT(Desc ORDER BY Desc)AS varchar2_ntt))AS Vals FROM B GROUP BY pid;所有其他技术都比较慢。
您可以只编辑答案以包括相关信息。
我在编辑中为时已晚,这就是为什么我再次添加它。抱歉,我是新来的,刚开始了解它。
在运行选择查询之前,请运行以下命令:
SET SERVEROUT ON SIZE 6000
SELECT XMLAGG(XMLELEMENT(E,SUPLR_SUPLR_ID||',')).EXTRACT('//text()')"SUPPLIER"
FROM SUPPLIERS;
我使用LISTAGG,但是将此字符串返回波斯字符串!
我的查询:
SELECT
listagg(DESCRIPTION,' , ') WITHIN GROUP (ORDER BY DESCRIPTION)
FROM
B_CEREMONY
结果:
'A7'1 , ,4F
请帮我。
哇,这个解决方案是可行的:
SELECT listagg(CONVERT(DESCRIPTION, 'UTF8', 'AL16UTF16'),' , ') WITHIN GROUP
(ORDER BY DESCRIPTION)
FROM B_CEREMONY;
试试这个代码:
SELECT XMLAGG(XMLELEMENT(E,fieldname||',')).EXTRACT('//text()')"FieldNames"
FROM FIELD_MASTER
WHERE FIELD_ID > 10 AND FIELD_AREA != 'NEBRASKA';
在选择要串联的位置,调用SQL函数。
例如:
SELECT PID, dbo.MyConcat(PID)
FROM TableA;
然后对于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
函数头语法可能是错误的,但是该原理确实有效。
这对Oracle无效