MS SQL查询结果合并为无尾随分隔符的长分隔字符串问题

Got it, let's fix that query so you get exactly the single long string without a trailing delimiter, in the ID|NAME|ID|NAME... format you need.

For SQL Server 2017 and Later (Using STRING_AGG) #

This is the cleanest approach since STRING_AGG was built specifically for concatenating values across rows without extra trailing delimiters. We'll first combine each row's ID and COMPANY into an ID|COMPANY segment, then join all those segments with | to get your final string:

SELECT STRING_AGG(CONCAT(ID, '|', COMPANY), '|') AS ORGLIST
FROM vw_PreferredMail
WHERE ... -- Keep your existing WHERE conditions here

Notes: #

• CONCAT is safer than using + because it automatically handles NULL values (replacing them with empty strings instead of making the entire segment NULL).
• STRING_AGG doesn’t add a trailing delimiter, so you won’t end up with an extra | at the end of your final string.

For Older SQL Server Versions (Pre-2017, Using FOR XML PATH) #

If you're stuck on a version before STRING_AGG existed, use this method with STUFF and FOR XML PATH to build the string and strip the leading delimiter:

SELECT STUFF(
    (SELECT '|' + CONCAT(ID, '|', COMPANY)
     FROM vw_PreferredMail
     WHERE ... -- Same WHERE conditions as above
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 1, ''
) AS ORGLIST

Notes: #

• The subquery generates a string starting with |, so STUFF removes that first character to get a clean starting ID.
• Using TYPE and .value('.', 'NVARCHAR(MAX)') prevents XML escaping of special characters (like &, <, or >) in your COMPANY names.

Handling NULL Values #

If you want to skip rows where either ID or COMPANY is NULL, add this to your WHERE clause:

AND ID IS NOT NULL AND COMPANY IS NOT NULL

内容的提问来源于stack exchange，提问作者whispers