Oracle行列互换总结

 

Oracle行列互换总结

 

1. 多行换成一行

    col1  col2            col1  col2

    a       1

    a       2     转换后   a     1,2,3

    a       3

   A. sys_connect_by_path 函数

        SELECT NAME, ltrim(MAX(sys_connect_by_path(userid, ',')), ',') userid   FROM(SELECT NAME, userid,row_number() over(PARTITION BY NAME ORDER BY userid) rn  FROM test)  START WITH rn = 1 CONNECT BY rn - 1 = PRIOR rn AND NAME = PRIOR NAME GROUP BY NAME ORDER BY NAME;

    B.wmsys.WM_CONCAT函数

       select  NAME,wmsys.WM_CONCAT(userid)  from xj_class group by  NAME;

2. 一行换多行

       col                                   col

       1,2,3       转换后               1

                                               2

                                               3

   

       select substr(subject_id,instr(subject_id,',',1,rownum)+1,
             instr(subject_id,',',1,rownum+1)-instr(subject_id,',',1,rownum)-1) subject_id,rownum
       from (select ','||subject_id||',' as subject_id
             from tea_class_subject where teacher_id=61 and class_id=16641)
       connect by rownum<length(translate(subject_id,','||subject_id,','))

 

 

注：使用行列互换函数貌似很占内存。