【ORACLE】行转列，列转行

SELECT LEVEL AS ID FROM DUAL CONNECT BY LEVEL < 7


with tab as(
select 'USA,England,China,Japan'  id from dual
)
select substr(','||id||',',instr(','||id||',',',',1,level)+1,
          instr(','||id||',',',',1,level+1)-instr(','||id||',',',',1,level)-1) newid
from tab
connect by
level <= length(','||id||',') - length(replace(','||id||',', ',', ''))-1


--10g的話，用正則表達式簡單點:
with tab as(
select 'USA,England,China,Japan'  id from dual
)
select regexp_substr(id,'[^,]+',1,level) as cl1
from tab
connect by

level<=length(id)-length(replace(id,',',''))+1

SELECT     MAX (SUBSTR (SYS_CONNECT_BY_PATH (num, ','), 2)) VALUE
      FROM (SELECT   b.dwbm, COUNT (a.senddeptid) num
                FROM (SELECT x.*
                        FROM sys_form_recipient t, sys_form_addresser x
                       WHERE t.reportinfoid = x.reportinfoid
                         AND t.readstate = '未读'
                         AND x.mkbh = '6') a,
                     (SELECT *
                        FROM sys_com_zzjg) b
               WHERE b.dwbm = a.senddeptid(+)
            GROUP BY b.dwbm
            ORDER BY TO_NUMBER (b.dwbm) ASC)
START WITH dwbm = 1
CONNECT BY dwbm = ROWNUM