oracle中替代wm_concat函数的方法
racle数据库中，不许用wm_concat函数，合并列，希望得到与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;
-----------------------方法一------------------------------------------------------------
11g及以上用这个函数 LISTAGG
select s_type,listagg(s_mc,'，') within group (order by n_id) mc from t_thz_1
group by s_type
;
如果是低版本的话可以参考以下方法：
第一步（创建类型）：
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 函数来查询，collect是在Oracle 10g中新增加了一个聚合函数）：
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
坚果 核桃,巴旦木
蔬菜 西兰花,茼蒿,茄子
水果 葡萄,哈密瓜,火龙果,香瓜
-----------------------方法二------------------------------------------------------------ 如果值不多,也可以用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 )
