How do I concatenate fields into a single field when populating a SQL database (Oracle)
I need to plug a load of data from a separate program into a single table (in Oracle SQL Developer). This transfer of data is going to be in one direction, meaning the system will just occasionally dump a load of data in the table, replacing what was there before. I therefore don't have to worry about being able to update individual fields. I also can't modify how this system transfers the data into my table, which means I am stuck with mapping its fields to my column headers (it's just sending the data using INSERTs behind the scenes).
I want the table to have a unique TRANSACTION_ID column. However, each TRANSACTION_ID might have multiple TRANSACTION_TYPEs, so I will receive multiple rows for each ID with a different TRANSACTION_TYPE. e.g:
INSERT INTO TEST_TABLE (TRANSACTION_ID, TRANSACTION_TYPE) VALUES (1000, TT35) INSERT INTO TEST_TABLE (TRANSACTION_ID, TRANSACTION_TYPE) VALUES (1000, TT40) INSERT INTO TEST_TABLE (TRANSACTION_ID, TRANSACTION_TYPE) VALUES (1000, TT12) INSERT INTO TEST_TABLE (TRANSACTION_ID, TRANSACTION_TYPE) VALUES (1001, TT12) ......etc.
I want to concatenate these into a single field separated by commas, so the final table would look like:
TRANSACTION_ID TRANSACTION_TYPES ----------------------------------------- 1000 TT35,TT40,TT12 1001 TT12 1002 TT40,TT23
I realise that this is de-normalising the data, but since I do not need to update it I am not overly concerned.
I understand the way to do this usually is by using a MERGE, but since I am stuck with the INSERT actions of the source system I cannot use this. Is it possible to do this using a trigger? I've run into mutating table errors etc. in my previous attempts.
The last resort might be to store the TRANSACTION_TYPEs in a separate table, treat the data, and then delete the second table, but that seems ridiculously over-complicated.
Is there a straight-forward way of doing this that I'm missing?
This is too long for a comment.
You probably could do this with a trigger, but I wouldn't recommend it. The trigger would need to replace the insert, sometimes doing an insert and sometimes concatenating the values.
Two other options. First, load the data into a staging table and then create a new table that your process.
The second is to just ignore the problem and use list_agg() to bring the data together when you are querying it.