澳门威利斯人_威利斯人娱乐「手机版」

来自 威利斯人娱乐 2019-09-28 21:11 的文章
当前位置: 澳门威利斯人 > 威利斯人娱乐 > 正文

如何取得一个表的所有字段名用逗号分割

自打 Oracle 9i 开始,就能够透过SYS_CONNECT_BY_PATH 函数完成将从父节点到当下
行内容以“path”可能档案的次序成分列表的样式体现出来。
收获三个表的具有字段名,用逗号分割。
select max(substr(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','),2)) col from (
select COLUMN_NAME,column_id from user_tab_columns where table_name='&表名')
start with column_id=1
connect by column_id=rownum;


行转列之一 太简单不说了


例1:

图片 1

 1  with v_data as
 2  (select '306A' certsn, 'A' type, '10951580' value, 20080504 notbefore, 20170803 notafter    from dual  union
 3   select '48C6' certsn, 'B' type, 'X,2,10013856' value, 20090629 notbefore, 20180616 notafter    from dual  union
 4   select '48C6' certsn, 'A' type, '11260221' value, 20090629 notbefore, 20180616 notafter    from dual)
 5 select certsn,
 6        case when v_data.type = 'A' then max(value) else ' ' end A,
 7        case when v_data.type = 'B' then max(value) else ' ' end B,
 8        max(notbefore) notbefore,
 9        max(notafter) notafter
10   from v_data
11  group by certsn, type;

 

结果: 

图片 2

转置成

图片 3

 

 


行转列之二 wm_concat()


例1:

wm_concat()行转列而且用逗号分隔, 需求分组!

图片 4

1 select wm_concat(ename), --结果为类型clob
2        to_char(wm_concat(ename)),
3        dbms_lob.substr(wm_concat(ename), 4000)
4   from emp
5  where rownum < 5;

 

 

例2:

select c, listagg(a,'') within group(order by rownum) from test group by c;
with test as (
select 1 c,'西' a from dual union all
select 1 c,'安' a from dual union all
select 1 c,'的' a from dual union all
select 2 c,'天' a from dual union all
select 2 c,'气' a from dual union all
select 3 c,'好' a from dual 
)
select c, replace(to_char(wm_concat(a)),',','') FROM test group by c;

结果:

图片 5

或者

select c, listagg(a,'') within group(order by rownum) from test group by c;


 

行转列之三 listtagg()


Oracle 转门的行转置函数 listagg()

用法似乎聚合函数同样,通过Group by语句,把每种Group的一个字段,拼接起来。特别实惠。

 

语法:

       LISTAGG(<转置列列表>, '分隔符' ) WITHIN GROUP( O大切诺基DE奥迪Q7 BY <排体系列表>) 

       GROUP BY ...

 

例子:

源数据为

 图片 6

 

行转列:

方法1)使用group by分组

 1 with temp as (  
 2   select 'China' nation ,'Guangzhou' city from dual union all  
 3   select 'China' nation ,'Shanghai'  city from dual union all  
 4   select 'China' nation ,'Beijing'   city from dual union all  
 5   select 'USA'   nation ,'New York'  city from dual union all  
 6   select 'USA'   nation ,'Bostom'    city from dual union all  
 7   select 'Japan' nation ,'Tokyo'     city from dual  
 8 )  
 9 select nation,listagg(city,',') within GROUP (order by city)  
10 from temp  
11 group by nation;

结果:

图片 7

 

艺术2)不选用分组, 使用listagg over ( partition by 分组列)

with temp as(  
  select 'China' nation ,'Guangzhou' city from dual union all
  ...
)
select --nation, listagg(city,',') within GROUP (order by city) over (partition by nation) rank --使用分析函数代替分组
       distinct nation, listagg(city,',') within GROUP (order by city) over (partition by nation) rank --去重!
  from temp 
 order by nation; 

结果和下面SQL一致!

 

 


本文由澳门威利斯人发布于威利斯人娱乐,转载请注明出处:如何取得一个表的所有字段名用逗号分割

关键词: 澳门威利斯人 SQL Oracle开发