Oracle SQL Concatenate multiple entries with different conditions

Solution 1:

If you don't want to join the secondTable three times, you can use below alternative which is using Unpivoting clause to first unpivot the firstTable (columns ID* --> rows), and then joining it with the secondTable . All you have to do after that step is to group that result per "row" column, and use the Listagg aggregate function to concatenate texts as you need.

with firstTable_Unpivoted ( "Row", ID, source_column ) as (
    SELECT "Row", ID, source_column
    from firstTable
    unpivot (
        ID for source_column in (
            ID1 as 'ID1'
        ,   ID2 as 'ID2'
        ,   ID3 as 'ID3'
        )
    )
)
select 
    FTU."Row"
  , listagg(ST.Text, ' ') within group (order by FTU.ID) ConcatinatedText
from 
    firstTable_Unpivoted FTU
left join 
    secondTable ST
        on FTU.ID = ST.ID
group by FTU."Row"
;

demo on db<>fiddle