merge several records with the same call_id into one

Question

There is a table_verif table where I need to insert data, here is an example of fields and records for this table:

call_id | datetime  |     process_name     | param_1 | param_2      | param_3 | param_ 4 |
------------------------------------------------------------------------------------------
1234567 | 29.12.2022| verification process | greeting| presentation | result  | waiting  |
---------------------------------------------------------------------------------------

And there is my table my_table with entries:

call_id  | datetime   |  process_name |       param_1        |
--------------------------------------------------------------
1234567  | 29.12.2022 |  complaints   | Establishing contact | 
--------------------------------------------------------------
1234567  | 29.12.2022 |  complaints   | Identification       | 
--------------------------------------------------------------
1234567  | 29.12.2022 |  complaints   | Num specification    | 
--------------------------------------------------------------
1234567  | 29.12.2022 |  complaints   | Data transfer        | 

I need to translate these four records into a table_verif format:

call_id| datetime |process_name|    param_1         | param_2      | param_3         | param_4
--------------------------------------------------------------------------------------------
1234567|29.12.2022|complaints  |Establishing contact|Identification|Num specification|Data transfer

I took it shortly to param_4, but in practice it starts with param_1 and ends with ..param_9

Answer 1

You can pivot a result set (more explanation here), but you need to have a value to pivot on and provide the ordering for the result. For example you could start with a query like:

select call_id, datetime, process_name, param_1,
  -- establish some ranking - use your own criteria
  row_number() over (partition by call_id, process_name order by datetime, param_1) as rn
from my_table

which with the sample data plus reverse-engineered data from your example table_verif row might produce:

CALL_ID	DATETIME	PROCESS_NAME	PARAM_1	RN
1234567	29-DEC-22	complaints	Data transfer	1
1234567	29-DEC-22	complaints	Establishing contact	2
1234567	29-DEC-22	complaints	Identification	3
1234567	29-DEC-22	complaints	Num specification	4
1234567	29-DEC-22	verification process	greeting	1
1234567	29-DEC-22	verification process	presentation	2
1234567	29-DEC-22	verification process	result	3
1234567	29-DEC-22	verification process	waiting	4

You may have different criteria to decide the parameter order, perhaps from a look-up table you haven't shown. However you get it, once you have a generated value like rn you can use that for the pivot:

select *
from (
  select call_id, datetime, process_name, param_1,
    -- establish some ranking - use your own criteria
    row_number() over (partition by call_id, process_name order by datetime, param_1) as rn
  from my_table
)
pivot (
  max(param_1)
  for (rn) in (1 as param_1, 2 as param_2, 3 as param_3, 4 as param_4, 5 as param_5)
)

CALL_ID	DATETIME	PROCESS_NAME	PARAM_1	PARAM_2	PARAM_3	PARAM_4	PARAM_5
1234567	29-DEC-22	complaints	Data transfer	Establishing contact	Identification	Num specification	null
1234567	29-DEC-22	verification process	greeting	presentation	result	waiting	null

fiddle

You can then use that as an insert ... select ... statement to populate table_verif, or if this is permanent data then you can turn the query into a view instead of duplicating it.

You could also pivot by listing all the possible param_1 values in the in() clause instead of generating a ranking value, but that would give you a much wider result, and doesn't seem to be what you want here.