oracle行列转换

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) tSTART WITH rn = 1CONNECT 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 UNIONselect 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 UNIONselect 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