Convert Rows or Arrays to Comma Delimited String

There are times when you need to take multiple rows and convert the data into a single comma separated text string. Here are some examples.

Sub-Select

select bp.value ,
 (
     select array_to_string(array_agg(r.documentno),',') from r_request r where r.c_bpartner_id = bp.c_bpartner_id and r.processed='N'
 )
 from c_bpartner bp

Notice how the above subselect uses two key functions:

• array_arg(...) - this aggregation function takes multiple rows and converts the data into an array.
• array_to_string(...) - this function takes an array and converts it to a delimited text string.

Entire Query

select array_to_string(array(
select concat('<a href="',chuboe_googlemap_location(chuboe_address_bploc(x.c_bpartner_location_id, ' ')),'" target="_blank">',chuboe_address_bploc(x.c_bpartner_location_id, ' '),'</a>') from c_bpartner_location x where c_bpartner_id = @C_BPartner_ID@
),'<br>')

Notice how the above select uses two key functions:

• array(...) - this function takes multiple rows from the inner select statement and converts the them to a delimited text string.
• array_to_string(...) - this function takes an array and converts it to a delimited text string.