I have a table called tblrespondent
and my data looks like this:
orclid13 | casenm | commentfld ------------------------------- 27527 2021JV0039 grandmother 27528 2021JV0039 of Liliana/ Possibly Deceased 27529 2021JV0039 Possibly deceased. 27530 2021JV0039 whereabout unknown. 27531 2021JV0039
I have several case numbers that have multiple rows like this and I need to move all the comments into the lowest orclid13
. The result would look like this with a semicolon in between each comment.
orclid13 | casenm | commentfld ------------------------------- 27527 2021JV0039 grandmother; of Liliana/ Possibly Deceased; Possibly deceased.; whereabout unknown. 27528 2021JV0039 27529 2021JV0039 27530 2021JV0039 27531 2021JV0039
How would I approach this? Many thanks in advance!
Advertisement
Answer
Assuming you need a select
query, this could be a way:
select orclid13, casenm, case when row_number() over (partition by casenm order by orclid13) = 1 then listagg (commentfld, ';') within group (order by orclid13) over (partition by casenm ) end as commentfld from tblrespondent
Basically, listagg
aggregates all the comments into a single string, row_number()
is used to determine whether you are on the first row of the casenm
set and this result is used by the case
to show the concatenated string only in the first row of the set.
If you need to update
your table, you can use the same logic above in a merge
:
merge into tblrespondent t using ( select orclid13, casenm, rowid, case when row_number() over (partition by casenm order by orclid13) = 1 then listagg (commentfld, ';') within group (order by orclid13) over (partition by casenm ) end as new_commentfld from tblrespondent ) X on (x.rowid = t.rowid) when matched then update set t.commentfld = X.new_commentfld