Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

Concatenate many rows into a single text string using SQL Server 2008

0.00/5 (No votes)
25 Feb 2012CPOL 9.8K  
How to concatenate many rows into a single text string using SQL Server 2008
SQL
declare @d1 datetime,@d2 datetime;

set @d1=GETDATE();
select STUFF((SELECT ' , '+ Convert(varchar,UserID)  
		FROM dbo.AuditLog   LRT  
		FOR XML PATH('')), 1, 2, '')  
set @d2=GETDATE();
print datediff(mcs,@d1,@d2)

set @d1=GETDATE();
DECLARE @Names VARCHAR(8000)  
SELECT @Names =ISNULL( @Names+',','')+Convert(varchar,UserID) FROM dbo.AuditLog 
SELECT @Names;
set @d2=GETDATE();
print datediff(mcs,@d1,@d2)

Using XML path() may take less time to execute big table data.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)