Oracle常用函数:nvl/nullif/case when/wm_concat/replace(三)
>insertintotest values(1,'c');
SQL>insertintotest values(2,'d');
SQL>insertintotest values(2,'e');
SQL>commit;
效果1 :列转行
SQL>selectwm_concat(name)fromtest;
WM_CONCAT(NAME)
------------------------------
a,b,c,d,e
效果2: 把结果里的逗号替换成"|"
SQL>selectreplace(wm_concat(name),',','|')fromtest;
REPLACE(WM_CONCAT(NAME),',','|')
-----------------------------------------------------------------------
a|b|c|d|e
效果3:按ID分组合并name
SQL>selectid,wm_concat(name)namefromtest groupbyid;
IDNAME
---------- ------------------------------
1 a,b,c
2 d,e
案例:我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法 当然有了,看我如果应用wm_concat来让这个需求变简单
SQL>select'create or replace view as select '|| wm_concat(column_name) ||' from dept'fromuser_tab_columnswheretable_name='DEPT';
'CREATEORREPLACEVIEWASSELECT'||WM_CONCAT(COLUMN_NAME)||'FROMDEPT'
--------------------------------------------------------------------------------
createorreplaceviewasselectDEPTNO,DNAME,LOCfromdept