oracle合并列值问题,不许用wm_concat函数,但希望得到和wm_concat一样的结果
0
悬赏园豆：100
[已解决问题]
解决于 2017-05-25 17:04
问题：oracle数据库中，不许用wm_concat函数，合并列，希望得到结果如下:
s_type s2
水果 葡萄,哈密瓜,香瓜,火龙果
蔬菜 西兰花,茼蒿,茄子
坚果 核桃,巴旦木
数据如下：
create table t_thz_1(
n_id number(10),
s_mc varchar2(100),
s_type varchar2(10)
);
truncate table t_thz_1;
insert into t_thz_1(n_id,s_mc,s_type)
select 1 n_id,'葡萄' s_mc,'水果' s_type from dual union all
select 2 n_id,'哈密瓜' s_mc,'水果' s_type from dual union all
select 3 n_id,'西兰花' s_mc,'蔬菜' s_type from dual union all
select 4 n_id,'核桃' s_mc,'坚果' s_type from dual union all
select 5 n_id,'茼蒿' s_mc,'蔬菜' s_type from dual union all
select 6 n_id,'香瓜' s_mc,'水果' s_type from dual union all
select 7 n_id,'巴旦木' s_mc,'坚果' s_type from dual union all
select 8 n_id,'茄子' s_mc,'蔬菜' s_type from dual union all
select 10 n_id,'火龙果' s_mc,'水果' s_type from dual
;
commit;
可以用case或其他方法得到 与wm_concat 一样的结果吗？
最佳答案
1
貌似高版本中有个替代函数 LISTAGG，如果是低版本的话可以参考以下方法：
第一步（创建类型）：
CREATE OR REPLACE TYPE tab_str IS TABLE OF VARCHAR2(4000);
第二步（创建函数）：
1 CREATE OR REPLACE FUNCTION fn_to_str( 2 p_str_tab IN tab_str, 3 p_separator IN VARCHAR2 DEFAULT ',' 4 ) 5 RETURN VARCHAR2 IS 6 v_ret_str VARCHAR2(4000); 7 BEGIN 8 FOR i IN 1..p_str_tab.COUNT LOOP 9 v_ret_str:=v_ret_str||p_separator||p_str_tab(i); 10 END LOOP; 11 RETURN v_ret_str; 12 END fn_to_str;
第三步（利用前两步创建的类型和函数及 COLLECT 函数来查询）：
SELECT t.s_type,LTRIM(fn_to_str(CAST(COLLECT(t.s_mc) AS tab_str)),',') s_names FROM demo.t_thz_1 t GROUP BY t.s_type;
查询结果如下：
S_TYPE S_NAMES ---------- ------------------------------ 坚果 核桃,巴旦木 蔬菜 西兰花,茼蒿,茄子 水果 葡萄,哈密瓜,火龙果,香瓜
收获园豆：100
嗯，这个好。原来collect是在Oracle 10g中新增加了一个聚合函数.平时用得很少.不太熟.学习了
如果值不多,也可以用case when这样写:
select s_type,substr(pt||hmg||xg||hlg||xlh||th||qz||ht||bdm,2) as s_mc from (
select s_type,
max(case when s_mc='葡萄' then ','||s_mc end) as pt,
max(case when s_mc='哈密瓜' then ','||s_mc end) as hmg,
max(case when s_mc='香瓜' then ','||s_mc end) as xg,
max(case when s_mc='火龙果' then ','||s_mc end) as hlg,
max(case when s_mc='西兰花' then ','||s_mc end) as xlh,
max(case when s_mc='茼蒿' then ','||s_mc end) as th,
max(case when s_mc='茄子' then ','||s_mc end) as qz,
max(case when s_mc='核桃' then ','||s_mc end) as ht,
max(case when s_mc='巴旦木' then ','||s_mc end) as bdm
from t_thz_1 t
group by s_type
)
其他回答(2)
0
http://bbs.csdn.net/topics/390733235 你可以看看
用java的方式明白,但用数据库的方法没有说明.恰巧我想用数据库的方法
@江清风: 为什么一定要在数据库成面执行呢，数据库往往都是性能瓶颈所在，太复杂的语句不建议在数据库上来处理
@YQZC: 因为俺不是做java开发滴
0
可以自定义聚合函数
重写wm_concat函数吗?
@江清风: 自定义的意思就是想怎么写就怎么写
