Click here to Skip to main content
Click here to Skip to main content

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

, 25 Feb 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

License

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

Share

About the Author

Sanjay K. Gupta
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 5+ years of software development career, he has worked on various technologies.
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 25 Feb 2012
Article Copyright 2012 by Sanjay K. Gupta
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid