Using Stuff to roll up data from multiple rows AND concatenate columns

Question

I've found similar questions on the site, but I'm still struggling with this. I have a table with information like the below:

AcctNo   ChargeOrder    ChargeCode
 ABC         1           Charge1
 ABC         2           Charge2
 ABC         3           Charge3

I'm trying to use the XML Path/STUFF functions to return the data like so:

AcctNo  Order/Code
ABC     1:Charge1 - 2:Charge2 - 3:Charge3

But I can't seem to figure out how to concatenate my chargeorder and chargecode AND STUFF them into a single field.

Answer 1

In SQL Server, you can use string_agg() -- in the more recent versions:

select acctno,
       string_agg(concat(ChargeOrder, ':', ChargeCode), ' - ')
from t
group by acctno;

In older versions, this would be phrased as:

select a.acctno,
       stuff( (select concat(' - ', ChargeOrder, ':', ChargeCode)
               from t t2
               where t2.acctno = a.acctno
               for xml path ('')
              ), 1, 3, ''
            )
from (select distinct acctno from t) a