大家好,
我有一下表mytab
id code name
1 a 张三
2 a 李四
3 c 王五
我想用一个函数或一条sql语句,来取出这样的数据:取出code='a'的数据,组成一个字符串。取出结果如:“张三、李四”我不想用游标来做,谢谢!
我有一下表mytab
id code name
1 a 张三
2 a 李四
3 c 王五
我想用一个函数或一条sql语句,来取出这样的数据:取出code='a'的数据,组成一个字符串。取出结果如:“张三、李四”我不想用游标来做,谢谢!
调试欢乐多
select replace(wm_concat(name),'、')
from mytab
where code='a'
select replace(wm_concat(name),',','、')
from mytab
where code='a'
--------9i
SQL> with tab as
2 (
3 select 1 id ,'a' code, '张三' name from dual union all
4 select 2 id ,'a' code, '李四' name from dual union all
5 select 3 id ,'c' code, '王五' name from dual
6 )
7 SELECT MAX(substr(sys_connect_by_path(t.name, '、'), 2)) str
8 FROM (SELECT code,
9 name,
10 row_number() over(PARTITION BY code ORDER BY name) rn
11 FROM tab
12 where code = 'a') t
13 START WITH rn = 1
14 CONNECT BY rn = PRIOR rn + 1
15 AND code = PRIOR code
16 ;STR
--------------------------------------------------------------------------------
张三、李四
----10g
SQL>
SQL> with tab as
2 (
3 select 1 id ,'a' code, '张三' name from dual union all
4 select 2 id ,'a' code, '李四' name from dual union all
5 select 3 id ,'c' code, '王五' name from dual
6 )
7 select replace(wm_concat(name),',','、') from tab where code = 'a'
8 ;REPLACE(WM_CONCAT(NAME),',','?
--------------------------------------------------------------------------------
张三、李四SQL>