SQL & PL/SQL

Hello,

from a 3rd Party application i have no nice data contens. I try to explane with this example:

with tab_def as (

      select 'CASE1'as CASE,'KEY' as KEY,'COL1,COL2' as cols from DUAL

      union

      select 'CASE2'as CASE,'KEY' as KEY ,'FIRSTNAME,SURNAME,CITY' as cols from dual)

select * from tab_def;

|CASE  |KEY|COLS         |

---------------------------------

CASE1|KEY|COL1,COL2|

CASE2|KEY|FIRSTNAME,SURNAME.CITY|

with tab_data as (

     select 'CASE1' as CASE,'4711' as KEY ,'1,2' as cols from dual

     union

     select 'CASE1','4712','1' from dual

     union

     select 'CASE1','4713','foo,baar' from dual

     union

     select 'CASE2','1','Steven,King,NY' from dual

     union

     select 'CASE2','2','James,Kirk' from dual

     union

     select 'CASE2','3',',Spock,Vulcan' from dual

     )

select * from tab_data;

How can a SQL or generic Table-pipeline funktion be implementet to get the following result:

When i give 'CASE1' the result of te SQL sould be:

|KEY|COL1|COL2

-----------------------

|4711|1|2|

|4712|1||

|4713|foo|baar|

When i give 'CASE2' the result of the SQL sould be:

|KEY|FIRSTNAME|SURNAME|CITY

--------------------------------------------

|1|Steven|King|NY|

|2|James|Kirk||

|3||Spock|Vulcan|

In case it is not possible to use / include the Information of tab_def i would be totaly be happy

to get the posibility to split the data of tab_data (cols) into colums - but i do not kow how to dynamicly split into an unknown number of columns.

Many Thanks

Frank