I have a table which has a column called 'species' this lists the species present at particular sites. At the moment this is a comma separated list for each site:
Create table species_test (
site_name number,
species varchar2(255));
insert into species_test values (1, 'Gallirallus_philippensis_assimilis, Botaurus_poiciloptilus, Cyclosorus_interruptus');
insert into species_test values (2, 'Pomaderris_rugosa');
insert into species_test values (3, 'Ptisana_salicina, Anguilla_dieffenbachii');
What I want to do is normalize this data by having each species on a separate row so that it looks like:
Create table species_testv2 (
site_name number,
species varchar2(255));
insert into species_testv2 values (1, 'Gallirallus_philippensis_assimilis');
insert into species_testv2 values (1, 'Botaurus_poiciloptilus');
insert into species_testv2 values (1, 'Cyclosorus_interruptus');
insert into species_testv2 values (2, 'Pomaderris_rugosa');
insert into species_testv2 values (3, 'Ptisana_salicina');
insert into species_testv2 values (3, 'Anguilla_dieffenbachii');
Is this possible to do this with a query? At the moment I'm going through and normalizing it manually. This is just a sample - the actual dataset is much larger.
Any help would be much appreciated.