Concatenate all records into one line text

Published on: 21st Feb 2013

Updated on: 18th May 2021

This is how we done it

For concatenating all records into one line text, you need a variable with NVARCHAR(MAX) or VARCHAR(MAX) type. The following query concatenates all the product codes and stores in @s variable.

declare @s nvarchar(max)

select
    @s = coalesce(@s + ',' + prod_code, prod_code)   
from tb_prod

select @s

This statement is handy for the following,

• Generate audit log that contains the changes.
• Generate value for mail merge process.
• Generate information for troubleshooting.

Related posts

• Create user reminder

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.