oracle行列转换

[baidu_18698117]

怎么样把上图的数据转换成下面的格式，按照年份、月份，且把月份里面的日期分开排列

[卖水果的net]

SQL> 
SQL> create table test(Y varchar(4), M1 varchar(30), M2 varchar(30), M3 varchar(30));
Table created
SQL> begin
  2      insert into test values('2016','1,3,5','2,4,6','55,77,AA');
  3      insert into test values('2017','5,7,9','6,8,9,13','11,22,88,90');
  4  end;
  5  /
PL/SQL procedure successfully completed
SQL> with m as (
  2      select *
  3      from test
  4      unpivot (V for MM in (M1,M2,M3))
  5  )
  6  select Y, MM, regexp_substr(V,'[^,]+', 1, level) val
  7  from m
  8  connect by level <= regexp_count(v,',')+1
  9  and prior y = y and prior mm = mm
 10  and prior dbms_random.value is not null;
Y    MM VAL
---- -- ------------------------------------------------------------
2016 M1 1
2016 M1 3
2016 M1 5
2016 M2 2
2016 M2 4
2016 M2 6
2016 M3 55
2016 M3 77
2016 M3 AA
2017 M1 5
2017 M1 7
2017 M1 9
2017 M2 6
2017 M2 8
2017 M2 9
2017 M2 13
2017 M3 11
2017 M3 22
2017 M3 88
2017 M3 90
20 rows selected
SQL> drop table test purge;
Table dropped

SQL>