连接 Oracle 中 SQL 查询的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14243131/
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
Concatenate results from a SQL query in Oracle
提问by Ramie
I have data like this in a table
我在表中有这样的数据
NAME PRICE
A 2
B 3
C 5
D 9
E 5
I want to display all the values in one row; for instance:
我想在一行中显示所有值；例如：
A,2|B,3|C,5|D,9|E,5|
How would I go about making a query that will give me a string like this in Oracle? I don't need it to be programmed into something; I just want a way to get that line to appear in the results so I can copy it over and paste it in a word document.
我将如何进行查询，在 Oracle 中为我提供这样的字符串？我不需要将它编程为某些东西；我只是想要一种方法让该行出现在结果中，以便我可以将其复制并粘贴到 Word 文档中。
My Oracle version is 10.2.0.5.
我的 Oracle 版本是 10.2.0.5。
采纳答案by Art
-- Oracle 10g --
-- 甲骨文 10g --
SELECT deptno, WM_CONCAT(ename) AS employees
FROM scott.emp
GROUP BY deptno;
Output:
10 CLARK,MILLER,KING
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
回答by Kyle
I know this is a little late but try this:
我知道这有点晚了，但试试这个：
SELECT LISTAGG(CONCAT(CONCAT(NAME,','),PRICE),'|') WITHIN GROUP (ORDER BY NAME) AS CONCATDATA
FROM your_table
回答by beder
Usually when I need something like that quickly and I want to stay on SQL without using PL/SQL, I use something similar to the hack below:
通常，当我快速需要类似的东西并且我想在不使用 PL/SQL 的情况下继续使用 SQL 时，我会使用类似于下面的 hack 的东西：
select sys_connect_by_path(col, ', ') as concat
from
(
select 'E' as col, 1 as seq from dual
union
select 'F', 2 from dual
union
select 'G', 3 from dual
)
where seq = 3
start with seq = 1
connect by prior seq+1 = seq
It's a hierarchical query which uses the "sys_connect_by_path" special function, which is designed to get the "path" from a parent to a child.
这是一个分层查询，它使用“sys_connect_by_path”特殊函数，该函数旨在获取从父级到子级的“路径”。
What we are doing is simulating that the record with seq=1 is the parent of the record with seq=2 and so fourth, and then getting the full path of the last child (in this case, record with seq = 3), which will effectively be a concatenation of all the "col" columns
我们正在做的是模拟 seq=1 的记录是 seq=2 的记录的父级，因此是第四个，然后获取最后一个子级的完整路径（在本例中为 seq = 3 的记录），即将有效地串联所有“col”列
Adapted to your case:
适应您的情况：
select sys_connect_by_path(to_clob(col), '|') as concat
from
(
select name || ',' || price as col, rownum as seq, max(rownum) over (partition by 1) as max_seq
from
(
/* Simulating your table */
select 'A' as name, 2 as price from dual
union
select 'B' as name, 3 as price from dual
union
select 'C' as name, 5 as price from dual
union
select 'D' as name, 9 as price from dual
union
select 'E' as name, 5 as price from dual
)
)
where seq = max_seq
start with seq = 1
connect by prior seq+1 = seq
Result is: |A,2|B,3|C,5|D,9|E,5
结果是： |A,2|B,3|C,5|D,9|E,5
回答by Ben
As you're in Oracle 10g you can't use the excellent listagg(). However, there are numerous other string aggregation techniques.
由于您在 Oracle 10g 中，您无法使用优秀的listagg(). 但是，还有许多其他字符串聚合技术。
There's no particular need for all the complicated stuff. Assuming the following table
没有特别需要所有复杂的东西。假设下表
create table a ( NAME varchar2(1), PRICE number);
insert all
into a values ('A', 2)
into a values ('B', 3)
into a values ('C', 5)
into a values ('D', 9)
into a values ('E', 5)
select * from dual
The unsupported function wm_concatshould be sufficient:
不受支持的功能wm_concat应该足够了：
select replace(replace(wm_concat (name || '#' || price), ',', '|'), '#', ',')
from a;
REPLACE(REPLACE(WM_CONCAT(NAME||'#'||PRICE),',','|'),'#',',')
--------------------------------------------------------------------------------
A,2|B,3|C,5|D,9|E,5
But, you could also alter Tom Kyte's stragg, also in the above link, to do it without the replace functions.
但是，您也stragg可以在上面的链接中更改 Tom Kyte 的，以在没有替换功能的情况下执行此操作。
回答by Nick Krasnov
Here is another approach, using modelclause:
这是另一种方法，使用model子句：
-- sample of data from your question
with t1(NAME1, PRICE) as(
select 'A', 2 from dual union all
select 'B', 3 from dual union all
select 'C', 5 from dual union all
select 'D', 9 from dual union all
select 'E', 5 from dual
) -- the query
select Res
from (select name1
, price
, rn
, res
from t1
model
dimension by (row_number() over(order by name1) rn)
measures (name1, price, cast(null as varchar2(101)) as res)
(res[rn] order by rn desc = name1[cv()] || ',' || price[cv()] || '|' || res[cv() + 1])
)
where rn = 1
Result:
结果：
RES
----------------------
A,2|B,3|C,5|D,9|E,5|
回答by bonCodigo
Managed to get till here using xmlagg: using oracle 11G from sql fiddle.
设法使用 xmlagg 到达这里：使用 sql fiddle 中的 oracle 11G。
Data Table:
数据表：
COL1 COL2 COL3
1 0 0
1 1 1
2 0 0
3 0 0
3 1 0
SELECT
RTRIM(REPLACE(REPLACE(
XMLAgg(XMLElement("x", col1,',', col2, col3)
ORDER BY col1), '<x>'), '</x>', '|')) AS COLS
FROM ab
;
Results:
结果：
COLS
1,00| 3,00| 2,00| 1,11| 3,10|
* SQLFIDDLE DEMO
* SQLFIDDLE 演示
回答by Woot4Moo
Something like the following, which is grossly inefficient and untested.
类似于以下内容，这是非常低效且未经测试的。
create function foo returning varchar2 as
(
declare bar varchar2(8000) --arbitrary number
CURSOR cur IS
SELECT name,price
from my_table
LOOP
FETCH cur INTO r;
EXIT WHEN cur%NOTFOUND;
bar:= r.name|| ',' ||r.price || '|'
END LOOP;
dbms_output.put_line(bar);
return bar
)
