65.9K
CodeProject is changing. Read more.
Home

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

How to concatenate many rows into a single text string using SQL Server 2008

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.