SQL/PLSQL:行转列总结_20170822

---

行转列之零 group by + case 太简单不细说了

---

例1:

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

结果

行转列之一 wm_concat()

---

例1:

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

select wm_concat(ename), --结果为类型clob

       to_char(wm_concat(ename)),

       dbms_lob.substr(wm_concat(ename), 4000)

  from emp

 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;

结果:

或者

select c, listagg(a,'') within group(orderby rownum)from testgroupby c;

---

行转列之二 listtagg()

---

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

语法:

        LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX) GROUP BY ...

用法就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来。非常方便。

例子:

源数据为

行转列:

1)使用group by分组

with temp as(  

  select 'China' nation ,'Guangzhou' city from dual union all  

  select 'China' nation ,'Shanghai' city from dual union all  

  select 'China' nation ,'Beijing' city from dual union all  

  select 'USA' nation ,'New York' city from dual union all  

  select 'USA' nation ,'Bostom' city from dual union all  

  select 'Japan' nation ,'Tokyo' city from dual  

)  

select nation,listagg(city,',') within GROUP (order by city)  

from temp  

group by nation;

结果:

2)高级用法不使用group by分组的情况:

使用分析函数, 即listagg + over(paratition by 分组列)

with tempas(  

  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一致!

总结：LISTAGG()把它当作SUM()函数来使用就可以了。

---

(转发)行转列之三 xmlagg() + xmlparse()

---

为解决 listagg() 字符串连接的结果过长问题

语法:

listagg(<转置列>, '<连接符>') within GROUP (order by <排序列>)

等价于

xmlagg(xmlparse(content <转置列> || '<连接符>' wellformed) order by <排序列>).getstringval()

拍序列随意

还是上面例子:

with temp as(  

  select 'China' nation ,'Guangzhou' city from dual union all  

  select 'China' nation ,'Shanghai' city from dual union all  

  select 'China' nation ,'Beijing' city from dual union all  

  select 'USA' nation ,'New York' city from dual union all  

  select 'USA' nation ,'Bostom' city from dual union all  

  select 'Japan' nation ,'Tokyo' city from dual  

)  

select nation, --转置列

      listagg(city,',') within GROUP (order by city) as listagg,

      xmlagg(xmlparse(content city || ',' wellformed) order by city).getstringval(), 

      to_char(substr(xmlagg(xmlparse(content city || ',' wellformed) order by city).getclobval(),1,4000)) --!!!

      -- .getclobval() 结果集类型为clob 用 to_char()转置 

from temp  

group by nation;

例3.2:

   select xmlagg(xmlparse(content wm_concat(ename) || ', ' wellformed) order by 1) .getstringval()     from emp    where rownum < 5    group by '必须有'; --分组列这里无所谓但必须要

结果:

---

行转列之四 SYS_CONNECT_BY_PATH()

---

层次查询中

函数 SYS_CONNECT_BY_PATH(列名,'连接符') 能取回叶子节点到根节点的全路径

例子3.1:取一张表的所有字段

select max(substr(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','), 2)) col

  from (select COLUMN_NAME, column_id

          from user_tab_columns

        where table_name = upper('&表名'))

start with column_id = 1

connect by column_id = rownum;

其中:

1)SYS_CONNECT_BY_PATH(COLUMN_NAME, ',')  --取表的所有列名, ','逗号分隔

2)substr(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','), 2) --去除第一个多余的','

3)max(substr(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','), 2)) --全表分区取max,即取最长的一条,即取出表的所有列名 , 且返回的是串类型的 不是clob类型的

例3.2:源数据为

使用sys_connect_by_path() 开始"拼接"

start with 子句是必须的

with v_data as (

  select ename v from emp where rownum < 5

) ,v_row_data as (

  select rownum rn,t.* FROM v_data t

) select rn,v,sys_connect_by_path(v,',') FROM v_row_data r 

start with rn = 1

connect by level = rn ;

行转列: 最终版,start with 使用了子查询

with v_data as (

  select ename v from emp where rownum < 5

) ,v_row_data as (

  select rownum rn,t.* FROM v_data t

) select max(trim(leading ',' from sys_connect_by_path(v,','))) FROM v_row_data r 

start with rn = (select min(rn) from v_row_data) --start with 使用了子查询

connect by level = rn ;