How to write SQL code that does not add a separator to the end of the concatenated list

[heinepeople]

Hello,

I often use the following SQL code to create a calculated field which concatenates elements of a list with a character separating them (here is it a vertical bar "|"). For instance, here I am creating a list of all the common names for a given plantID (CPCNumber) in the table tblPlantCommonNames. Is there a way to create this code but remove the character from the end of the list?

SELECT CAST( CommonName AS nvarchar) + N' | ' FROM  tblPlantCommonName  WHERE CPCNum = [@field:CPCNumer] for xml path(N'')

 

[Vitalikssssss]

Hi @heinepeople ,

You may try to add STUFF statement as described in this topic .

STUFF(
    (SELECT ' | ' + CAST (CommonName AS nvarchar)
    FROM tblPlantCommonName
    WHERE CPCNum = target.[@field:CPCNumer]
    FOR XML PATH (''))
, 1, 2, '')

Hope this helps.

Regards,

vitalikssssss

[Kurumi]

Hi - Just an addition to this, if you would like to display multiple values (comma and quotation marks separated), you can use this formula:

STUFF((SELECT ', ' +'"'+ (FIELDNAME) +'"' FROM TABLE_NAME FOR XML PATH ('')), 1, 1, '')

Result:
"test1.com", "test2.com", "test3.com", "test4.com"