wm_concat

 

wm_concat可以把列值用“，”分隔开，而且是显示成一行，一般会与group by并用

而“||”作用是把多列合并成一列

显示clob字段 

select dbms_lob.substr(<clob>)  from table

select 'create or replace view as select ' || wm_concat(column_name) ||
       ' from APP_USER' sqlStr
  from user_tab_columns
 where table_name = 'APP_USER';

 

 pivot 列转行

 

select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果' 苹果, '橘子', '葡萄', '芒果'));

 

 

unpivot 行转列

 

 

select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )