1、多行转换单列字符串
比如:
需要转换成:
1 b,z,x
2 c,x
写法1:
SELECT t.rank ,max(substr(sys_connect_by_path(t.name, ','), 2))
FROM (SELECT rank, name, row_number() over(PARTITION BY rank order by rowid ) rn
FROM temptest2) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND rank = PRIOR rank
GROUP BY t.rank;
sys_connect_by_path会组织一条路径,比如,a,b,c 通过substr再截取会有a
A,b
A,b,c
Max即得到结果
写法2:
select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From temptest2 t GROUP BY t.rank;
2、单列字符串转换成多行
即上面得到的结果再转换回去
写法1:
WITH T AS (
SELECT 1 id1,',tt,aa,bb' c1 from dual UNION
select 2 id1,',abc,xyz' c1 from dual
)
select distinct id1,regexp_substr(c1,'[^,]+',1,level) from t CONNECT by level < =length(c1)-length(replace(c1,',',''));
写法2:
WITH T AS (
SELECT 1 id1,',tt,aa,bb' c1 from dual UNION
select 2 id1,',abc,xyz' c1 from dual
)
select distinct id1,substr(c1||',',instr(c1||',',',',1,level)+1,instr(c1||',',',',1,level+1) - instr(c1||',',',',1,level)- 1 ),
level
from t CONNECT by level < =length(c1)-length(replace(c1,',',''));
通过connect by拆分成多行,拆成多少行由逗号决定。之后再通过substr结合level截取
3、多行转换成多列
通过max(case when... )或sum(case when...)
4、多列转换成多行
通过union all
.....
union all