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?

Thanks

Answers


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.


Need Your Help

Makefile with multiple source folders

c++ makefile

I'm trying to write a make file for my C++ project

how to convert string to ASCII in .NET

c# .net winforms

I'm trying to convert a string to ASCII and I added one button of convert to ASCII and 2 text boxes: