oracleの行转列及合并

上一篇 / 下一篇  2012-03-02 18:07:09 / 个人分类:orcle

1、wm_concat效果

SQL> create table test (id number,name varchar2(20));

SQL> insert into test values(1,'a');

SQL> insert into test values(1,'b');

SQL> insert into test values(1,'c');

SQL> insert into test values(2,'d');

SQL> insert into test values(2,'e');

SQL> commit;

SQL> select wm_concat(name) from test;

WM_CONCAT(NAME)

-------------------------------------------------------------------------

a,b,c,d,e
2、修改分隔符

SQL> select replace(wm_concat(name),',','|') from test;

REPLACE(WM_CONCAT(NAME),',','|')

-----------------------------------------------------------------------

a|b|c|d|e

3、分组合并字段

比如按ID分组合并name

SQL> select id,wm_concat(name) name from test group by id;

ID NAME

---------- ------------------------------
1 a,b,c
2 d,e

4、懒人的技巧

案例:我要写一个视图,类似"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 test' from user_tab_columns where table_name='TEST';--注意要大写
'CREATEORREPLACEVIEWASSELECT'||WM_CONCAT(COLUMN_NAME)||'FROMDEPT'

--------------------------------------------------------------------------------

create or replace view as select ID,NAME from dept


TAG:

 

评分:0

我来说两句

日历

« 2024-03-25  
     12
3456789
10111213141516
17181920212223
24252627282930
31      

数据统计

  • 访问量: 35904
  • 日志数: 104
  • 建立时间: 2011-10-10
  • 更新时间: 2012-04-12

RSS订阅

Open Toolbar