I'm in Oracle APEX and would like to create a new table from other existing one like this:

I have a column in a SONGS table that is:

And I want this in another table:

Also, from these 2 tables I would like to create another table or relationship to indicate on what songs have an artist participated.

Something like:

I want this third table to know in what song has participated an artist.

So im having troubles to create the 2nd and 3rd table from the first table.

CodePudding user response：

These are actually 2 different issues in a single question. Treat them as 2 different issues.

1. How to design your tables. This needs to be done first, you can import your data in them later. Create a songs table, an artist table and an intersect table (artist_songs) with foreign keys to both artists and songs. It should be pretty straightforward, there are thousands of examples on the web or, since you're using apex, using quicksql (SQL WOrkshop > Utilities > quick sql) for generating the tables is also an option.
2. Migrate the data from that base table into your new, normalized tables. Have a look at this similar stackoverflow question

You have provided very little info about what data you have available initially. It only shows artists in a comma separated form - it's hard to say how you would populate the songs or intersect table with that info.

CodePudding user response：

You can write and execute a procedure such as:

create or replace procedure "P_SONG_PARSE"
is
my_artist VARCHAR2(4000);
my_artist_id NUMBER;
my_temp VARCHAR2(4000);
my_pos NUMBER;

begin
    for rs in (select * from songs)
    loop
        my_temp := rs.ARTIST_NAME;
        my_pos := 0;
        loop
            my_pos := instr(my_temp,',');
            if my_pos = 0 then
                my_artist := trim(my_temp);
            else
                my_artist := trim(substr(my_temp,1,my_pos-1));
            end if;
            begin
                select ID
                into my_artist_id 
                from ARTISTS
                where ARTIST = my_artist;
                exception 
                    when no_data_found then
                        begin
                            select nvl(max(ID),0)   1
                              into my_artist_id
                              from ARTISTS;
                            insert into ARTISTS(ID,           Artist)
                                        values (my_artist_id, my_artist);
                        end;
            end; 
            insert into SONG_ARTISTS("Song ID", "Artist ID")
                              values(rs.ID,     my_artist_id);
            exit when my_pos = 0;
            my_temp := substr(my_temp,my_pos 1);
        end loop;
    end loop;    
end;