我有这样的数据在一个表
NAME PRICE
A 2
B 3
C 5
D 9
E 5
我想显示在一行中的所有值; 例如：
A,2|B,3|C,5|D,9|E,5|
我怎么会去作出查询，这将使我在甲骨文这样的字符串？ 我并不需要它来编程到的东西; 我只是想办法让该行出现在结果，所以我可以将其复制并粘贴文字文件内。
我的Oracle版本为10.2.0.5。
Answer 1:
- Oracle 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
Answer 2:
我知道这是一个有点晚，但试试这个：
SELECT LISTAGG(CONCAT(CONCAT(NAME,','),PRICE),'|') WITHIN GROUP (ORDER BY NAME) AS CONCATDATA
FROM your_table
Answer 3:
通常当我需要类似的东西很快，我想留在SQL不使用PL / SQL，我用类似下面的东西破解：
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
这是一个使用“SYS_CONNECT_BY_PATH”的特殊功能，其目的是让一个孩子从父“路径”分层查询。
我们正在做的是模拟与SEQ = 1的记录是序列记录的父= 2，所以第四，然后得到最后一个孩子的完整路径（在这种情况下，SEQ = 3的记录），其中将有效地将所有的“关口”列的串联
适应于您的情况：
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
结果是： |A,2|B,3|C,5|D,9|E,5
Answer 4:
正如你Oracle 10g中是不能使用的优秀listagg() 不过，也有许多其他的字符串聚合技术 。
还有所有的复杂的东西没有特别的需求。 假设下面的表
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
不支持的功能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
但是，你也可以改变汤姆凯特的stragg ，也是在上面的链接，这样做没有更换的功能。
Answer 5:
这是另一种方法，利用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
结果：
RES
----------------------
A,2|B,3|C,5|D,9|E,5|
SQLFiddle例
Answer 6:
设法到这里使用XMLAGG：使用Oracle 11g从SQL小提琴。
数据表：
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
;
结果：
COLS
1,00| 3,00| 2,00| 1,11| 3,10|
* SQLFIDDLE DEMO
- 参考上XMLAGG阅读
Answer 7:
类似下面的，这是非常低效的和未经考验的。
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
)
文章来源: Concatenate results from a SQL query in Oracle
