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.

Tag your question with the database you are using. – Gordon Linoff Jun 24 '20 at 21:34 — Gordon Linoff, Jun 24 '20 at 21:34

Gordon Linoff · Accepted Answer · 2020-06-24T21:46:58.933

0

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

edited Jun 24 '20 at 21:46

answered Jun 24 '20 at 21:35

Gordon Linoff

1,242,037
58
646
786

thank you. Unfortunately, I can't use the recent version (which looks FAR easier!). In the second version you provided, you are referencing 2 tables, t and t2...do I need this even though my data is all in the same table – user2938667 Jun 24 '20 at 21:43
@user2938667 . . . It is only referencing one table. `t2` and `a` are table aliases. – Gordon Linoff Jun 24 '20 at 21:47

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

1 Answers1