MSSQL - Concatenate all records into one line text
Published on: 21st Feb 2013
Updated on: 9th Jul 2025
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.
- Generate text in CSV file format.
Related posts
Back to #MSSQL blog
Back to #blog listing
Author
Lau Hon Wan, software developer.