Retrieve list of accounts with specific classification

Question

I want to retrieve a list of customer names and a list of the assigned account manager and region as registered using a custom property.

Using the following query, I get multiple lines per customer:

select accountclassifications_account_name
,      property_code_attr
,      property_description
from   exactonlinexml..accountclassifications 
where  property_code_attr in ( 'BO', 'REGION' )

But I would like to have one field with both the account manager and region. How to do this?

Answer 1

Using the group function listagg you can retrieve multiple values per customer account:

select accountclassifications_account_name
,      listagg(property_code_attr || ': ' || property_description) lst
from   exactonlinexml..accountclassifications 
where  property_code_attr in ( 'BO', 'REGION' )
group 
by     accountclassifications_account_name

When you need a different separator, you can specify it as a separate parameter to listagg.