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(subjec