循环分隔符数据ORACLE 11g

忽略奇怪的数据结构,如果你想要一个答案,它可能是

WITH test
     AS (SELECT 101 id,'5A101' name,'100411041204' date1,12000 salary FROM DUAL UNION ALL
         SELECT 1012 id,'5A102' name,'10041104' date1,12000 salary FROM DUAL)
    SELECT DISTINCT id,
                    name,
                    SUBSTR (date1, (LEVEL - 1) * 4 + 1, 4) date1
      FROM test
CONNECT BY LEVEL <= LENGTH (date1) / 4
  ORDER BY id, name, date1

此处,日期列的数据长度应为4的倍数。

例如,可以使用递归CTE:

^{SQLFiddle Demo}

with cte (id, name, date_, salary, dt, rn) as (
  select id, name, date_, salary, substr(date_, 1, 4), 1 from t
  union all
  select id, name, date_, salary, 
         substr(date_, rn * 4 + 1, 4), rn + 1 
  from cte where substr(date_, rn * 4 + 1, 4) is not null )
select id, name, dt, salary from cte 
  order by id, rn