How to concat values from different rows of the query?

Question

I have to concat the values of a column, grouping them according to the "rank" known from the same table.

TAB_LOOKUP

str_name rank
en       1
bs       2
cn       3
bf       4
co       5

Data present in source table

Source_Tab

id    str_name
1     co
1     en
1     bf
2     bs
2     co
3     bf
3     bs

Output data should look like

id   concat_str_name
1    en | bf | co    
2    bs | co
3    bs | bf 

Can I do it using some function like list_agg?

Answer 1

Is this what you want?

select listagg(str_name, ' | ') within group (order by rank)
from source_tab t join
     tab_lookup l
     on t.str_name = l.str_name
group by id;