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