Convert oracle column value into rows(and the reverse, rows to col)

Sometime I need to convert a col value(separated with some specific character(like :)) into rows:

Image that you have a column feeded by an Apex LOV, when user choose multiple values from the LOV, the return string will be a colon separated string. Later on you may need to convert this string into multiple rows so that you can put them back into some of your sql queries.

Here is the sql I used  for this:

  select server_name||'' aa from
  (
    with t as (select 'AA:NN:CC:DD:WW' server_list from dual)
         SELECT EXTRACT(column_value,'/e/text()') server_name from t x,
         TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<ROW><e>'||REPLACE(server_list,':','</e><e>')||'</e></ROW>'),'//e')))
  )

Replace the sql in red with your own table and columns.
If your separator is something else other than colon, you can simply replace the colon in the sql with your separator.

Here  is the reverse operation(convert rows to columns):

select 
   rtrim (xmlagg (xmlelement (e, wwid||'@xyz.com'|| ',')).extract ('//text()'), ',') email
from 
   majorevent_admin
where role='Admin'

where majorevent_admin is the table, one of the Col is people's WWID. another col is Role.