wm_concat函数⽤法
[sql]
1. ⾸先让我们来看看这个神奇的函数wm_concat(列名)，该函数可以把列值以","号分隔起来，并显⽰成⼀⾏，接下来上例⼦，看看这个神奇的函数如何应⽤
2. 准备测试数据
3. SQL> create table test(id number,name varchar2(20));
4. SQL> insert into test values(1,'a');
5. SQL> insert into test values(1,'b');
column函数的使用6. SQL> insert into test values(1,'c');
7. SQL> insert into test values(2,'d');
8. SQL> insert into test values(2,'e');
9. SQL> commit;
10. 效果1 : ⾏转列
11. SQL> select wm_concat(name) from test;
12. WM_CONCAT(NAME)
13. -------------------------------------------------------------------------
14. a,b,c,d,e
15. 效果2: 把结果⾥的逗号替换成"|"
16. SQL> select replace(wm_concat(name),',','|') from test;
17. REPLACE(WM_CONCAT(NAME),',','|')
18. -----------------------------------------------------------------------
19. a|b|c|d|e
20. 效果3：按ID分组合并name
21. SQL> select id,wm_concat(name) name from test group by id;
22. ID NAME
23. ---------- ------------------------------
24.    1 a,b,c
25.    2 d,e
26. 懒⼈扩展⽤法:
27. 案例：我要写⼀个视图，类似"create or replace view as select 字段1,...字段50 from tablename" ，基表有50多个字段，要是靠⼿⼯写太⿇烦了，有没有什么简便的⽅法? 当然有了，看
我如果应⽤wm_concat来让这个需求变简单
28. SQL> select'create or replace view as select '|| wm_concat(column_name) || ' from dept'from user_tab_columns where table_name='DEPT';
29. 'CREATEORREPLACEVIEWASSELECT'||WM_CONCAT(COLUMN_NAME)||'FROMDEPT'
30. --------------------------------------------------------------------------------
31. create or replace view as select DEPTNO,DNAME,LOC from dept