How convert a list of int to string with comma with function in SQL Server?
Convert to : "68,74,58,64,67"
-
8Google: "sql server aggregate string concat"Gordon Linoff– Gordon Linoff2017-04-23 15:01:44 +00:00Commented Apr 23, 2017 at 15:01
Add a comment
|
3 Answers
using the stuff() with select ... for xml path ('') method of string concatenation.
create table t (ProductId int);
insert into t values (68) ,(74) ,(58) ,(64) ,(67);
select
ProductIds = stuff((
select ','+convert(varchar(10),ProductId)
from t
for xml path (''), type).value('.','nvarchar(max)')
,1,1,'')
rextester demo: http://rextester.com/RZQF31435
returns:
+----------------+
| ProductIds |
+----------------+
| 68,74,58,64,67 |
+----------------+
edit: In SQL Server 2017+, you can use string_agg(), and the performance appears to be the same based on Jeffry Schwartz's article: Should I Replace My FOR XML PATH String Merges with String_agg?
3 Comments
John Cappelletti
Watching Invader Zim on DVD... Lots of yelling.
SqlZim
@JohnCappelletti I balance it out by keeping my sql lower case ^.^
Mizanur Rahman
while i apply where clause query return null
Use STRING_AGG
SELECT STRING_AGG(ProductId, ', ') AS ConcatenatedString
FROM Products
The first param is the column in the table (or variable). The 2nd param is the delimiter.
I think using stuff and for xml is an old way of doing it, this way is simplified.
Comments
You can use stuff and xml path for concatenation and selection data from after first comma..
select distinct stuff((select ',' + convert(char(2), productid) from #yourproductid for xml path('')),1,1, '')
from #yourproductid
Your table :
create table #yourproductid(productid int)
insert into #yourproductid (productid) values (68),(74),(58),(64),(67)
