Display result in a single line

[IamBlossom]

I have this formula that pulls up the record associated by ID, then display in a single line. 

SELECT CAST(Name AS nvarchar) + N', ' FROM PE_USER_TABLE PE_ID = target.[@field:PE_ID] for xml path(N'')

The result is Marco, Mary, Marie,

 

How can I remove the last comma "," ?

 

Thanks!

[cheonsa]

Hi @IamBlossom,

 What you can do about this is to add the "," before the result. 

You may try this one:

SELECT ABC = STUFF((SELECT ', ' + CAST(Name AS nvarchar) FROM PE_USER_TABLE WHERE PE_ID = target.[@field:PE_ID] FOR XML PATH ('')), 1, 1, '')

 

I am also using the same formula and I am getting my desired output. 

 

Cheers!  

[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"