Click here to Skip to main content
15,886,566 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
25 Feb 2012CPOL 9.7K   1  
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)


Written By
Technical Lead
India India
Sanjay K. Gupta is a Web/Software Developer Currently working with .Net Technologies. Although he knows VB language but, he mainly use C# language for programming. he is also interested in JavaScript and jQuery library. In his free time, he prefer to learn new technologies and write custom control libraries for ASP.NET.

In his 7+ years of software development career, he has worked on various technologies.

Comments and Discussions

 
-- There are no messages in this forum --