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 unio