oracle行列互转

Oracle常用函数

1：截取某字段最后一位 substr(字段名, -1)

select substr(‘abcdefg’, -1) from dual

2：字符串拼接 wm_concat

select substr(name, -1) from user

3： oracle合并行为一列 最后想要的效果：

001 n1;n2;n3;n4;n5
002 m1
003 t1;t2;t3;t4;t5;t6

myslq：selecct group_concat(name),pk_id from task group by name;

oracle:

select t.*,
(
select max(sys_connect_by_path(q,‘;’)) result from
(select no,q,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,q,row_number() over(order by no,q desc) rn from test)
)
start with no = t.no and rn1 is null connect by rn1 = prior rn
) value
from (select distinct no from test) t

4： 拆分某列数据为多行

SELECT DISTINCT ID, REGEXP_SUBSTR (字段名, ‘[^,]+’, 1, LEVEL) 别名, STUSEX FROM T_STU CONNECT BY LEVEL <= LENGTH (字段名) - LENGTH (REPLACE(字段名, ‘,’, ‘’)) + 1 ORDER BY ID;