Query which concatenates strings and puts into one column

Database
Highlighted

Query which concatenates strings and puts into one column

I'm working with tables 1 and 2 as shown below, respectively:

 

    --table1 (single field ItemNum)

    ItemNum
        1
        2

 

    --table2
    ID     ItemNum    ItemDesc
    1        1                 Desc 1
    2        2                 Desc 2
    1        3                 Desc 3
    2        4                 Desc 4
    1        5                 Desc 5

 

I want to join the two in a fashion that would give me the following result (more importantly, the field consisting of combined descriptions seperated by a comma but retaining the space between the word 'Desc' and the number)

 

    ID     ItemNum   CombDesc
    1           1           Desc 1, Desc 3, Desc 5
    2           2           Desc 2, Desc 4

 

So I know it has to be something like:

select t1.ID, t1.ItemNum, /*concatenated field goes here*/

from table1 as t1

left join table1 as t2 on t1.ID = t2.ID 

where t1.ItemNum in (select ItemNum from t1)

 

I found some previous examples using xmlagg, but I can't get it to work the way I can. Appreciate the assistance!


Accepted Solutions
Junior Contributor

Re: Query which concatenates strings and puts into one column

Trim(Trailing ',' FROM XmlAgg(ItemDesc || ',' ORDER BYItemNum) (VARCHAR(1000)))
1 ACCEPTED SOLUTION
2 REPLIES 2
Junior Contributor

Re: Query which concatenates strings and puts into one column

Trim(Trailing ',' FROM XmlAgg(ItemDesc || ',' ORDER BYItemNum) (VARCHAR(1000)))

Re: Query which concatenates strings and puts into one column

Thank you! This worked perfectly!