首先我要吐槽一下Oracle的wm_sys.wm_concat这个函数，为什么只能支持逗号分隔符呢?

　　PostgreSQL的string_agg就做得比较好，可以使用任意字符串作为分隔符。

　　Oracle行转列函数WMSYS.WM_CONCAT的使用实例demo

　　select * from itlife365_course a where name= '张三';

　　name 课程 score

　　张三 数学 99

　　张三 语文 89

　　张三 英语 93

　　上面所示的场景可用WMSYS.WM_CONCAT(a.name)把二行中的[课程]字段的值用","连接起来

　　如下所示：

　　select name, to_char(WMSYS.WM_CONCAT(a.课程))

　　from itlife365_course a

　　where name= '张三'

　　group by a.name;

　　注意：因为用WMSYS.WM_CONCAT转出的类型是clob的，所以我这用了to_char转了一下。

　　使用wmsys.wm_concat多列合成一列遇到问题

　　ORA-22813: 操作数值超出系统的限制

　　官方文档解释是总长度超过30k

　　请使用其他方法替代。

　　PostgreSQL不存在问题，最长可达到1GB。

　　PostgreSQL使用string_agg聚合函数即可达到同样的目的：

　　select name, string_agg(a.课程, ',')

　　from itlife365_course a

　　where name= '张三'

　　group by a.name;

　　如果用户不想修改代码，那么可以尝试自行创建一个名为WM_CONCAT的聚合函数，例子如下所示：

　　create schema WMSYS;

　　create or replace function WMSYS.sf_concat(text,text) returns text as $$

　　select case when $1 is not null then $1||','||$2 else $2 end;

　　$$ language sql called on null input;

　　create AGGREGATE WMSYS.wm_concat (text) (sfunc=WMSYS.sf_concat,stype=text);

　　测试如下:

　　postgres=# select reltype,wmsys.wm_concat(relname) from pg_class group by reltype order by reltype;

　　reltype | wm_concat

　　---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

　　------

　　0 | pg_oid_16388_index,pg_toast_2619_index,pg_authid_rolname_index,pg_attribute_relid_attnam_index,pg_attribute_relid_attnum_index,pg_toast_1255_index,ha_health_check_pkey,pg_toast_2606_index,pg_am_name_index,pg_am_oid_index,pg_am

　　op_fam_strat_index,pg_amop_opr_fam_index,pg_amop_oid_index,pg_amproc_fam_proc_index,pg_amproc_oid_index,pg_aggregate_fnoid_index,pg_toast_2618_index,pg_toast_2620_index,pg_toast_2609_index,pg_cast_oid_index,pg_cast_source_target_index,pg

　　_toast_2615_index,pg_toast_2964_index,pg_auth_members_role_member_index,pg_auth_members_member_role_index,pg_toast_2396_index,pg_toast_3596_index,pg_collation_oid_index,pg_collation_name_enc_nsp_index,pg_toast_2893_index,pg_database_datn

　　ame_index,pg_database_oid_index,pg_proc_oid_index,pg_proc_proname_args_nsp_index,pg_inherits_parent_index,pg_inherits_relid_seqno_index,pg_index_indrelid_index,pg_index_indexrelid_index,pg_operator_oid_index,pg_operator_oprname_l_r_n_ind

　　ex,pg_opfamily_am_name_nsp_index,pg_opfamily_oid_index,pg_opclass_am_name_nsp_index,pg_opclass_oid_index,pg_language_name_index,pg_language_oid_index,pg_largeobject_metadata_oid_index,pg_rewrite_oid_index,pg_rewrite_rel_rulename_index,pg

　　_trigger_tgconstraint_index,pg_trigger_tgrelid_tgname_index,pg_trigger_oid_index,pg_event_trigger_evtname_index,pg_event_trigger_oid_index,pg_description_o_c_o_index,pg_enum_oid_index,pg_enum_typid_label_index,pg_enum_typid_sortorder_ind

　　ex,pg_namespace_nspname_index,pg_namespace_oid_index,pg_conversion_default_index,pg_conversion_name_nsp_index,pg_conversion_oid_index,pg_depend_depender_index,pg_depend_reference_index,pg_tablespace_oid_index,pg_tablespace_spcname_index,

　　pg_pltemplate_name_index,pg_shdepend_depender_index,pg_shdepend_reference_index,pg_shdescription_o_c_index,pg_ts_config_cfgname_index,pg_ts_config_oid_index,pg_oid_16417_index,pg_type_oid_index,pg_user_mapping_oid_index,pg_user_mapping_u

　　ser_server_index,pg_ts_config_map_index,pg_ts_dict_dictname_index,pg_ts_parser_prsname_index,pg_ts_parser_oid_index,pg_ts_template_tmplname_index,pg_ts_template_oid_index,pg_extension_oid_index,pg_extension_name_index,pg_foreign_data_wra

　　pper_oid_index,pg_foreign_data_wrapper_name_index,pg_foreign_server_oid_index,pg_foreign_server_name_index,pg_foreign_table_relid_index,pg_default_acl_role_nsp_obj_index,pg_default_acl_oid_index,pg_seclabel_object_index,pg_shseclabel_obj

　　ect_index,pg_range_rngtypid_index,pg_synonym_oid_index,pg_variable_oid_index,pg_variable_varname_pkg_index,edb_dir_oid_index,edb_dir_name_index,edb_policy_oid_index,edb_policy_object_name_index,edb_partdef_oid_index,edb_partdef_pdefrel_i

　　ndex,edb_partition_oid_index,edb_partition_pdefid_index,pg_toast_16417_index,pg_oid_16431_index,pg_toast_16431_index,pg_toast_12506_index,pg_toast_12511_index,pg_toast_12516_index,pg_toast_12521_index,pg_toast_12526_index,pg_toast_12531_

　　index,pg_toast_13390_index,pg_toast_13857_index,system_waits_pk,pg_toast_13864_index,session_waits_pk,pg_toast_13871_index,session_waits_hist_pk,edb$stat_idx_pk,edb$stat_tab_pk,edb$stat_db_pk,edb$statio_idx_pk,edb$statio_tab_pk,pg_authid

　　_oid_index,pg_statistic_relid_att_inh_index,pg_type_typname_nsp_index,pg_largeobject_loid_pn_index,pg_class_oid_index,pg_class_relname_nsp_index,pg_toast_2604_index,pg_attrdef_adrelid_adnum_index,pg_attrdef_oid_index,pg_constraint_connam

　　e_nsp_index,pg_constraint_conrelid_index,pg_constraint_contypid_index,pg_constraint_oid_index,pg_db_role_setting_databaseid_rol_index,pg_ts_dict_oid_index,pg_synonym_synname_nspoid_index,pg_toast_12501_index,edb_partition_partrelid_index

　　,pg_toast_13397_index,pg_toast_13383_index,plsql_profiler_runs_pkey,pg_toast_13850_index,snap_pk

　　...