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.

Create user reminder

Back to #MSSQL blog

Back to #blog listing

Author

Lau Hon Wan, software developer.

Concatenate all records into one line text

This is how we done it

Related posts

Author