Using COALESCE function to make values separated with commas
DECLARE @List VARCHAR(8000)
SELECT @List = COALESCE(@List + ',', '') + CAST(OfferID AS VARCHAR)
FROM Emp
WHERE EmpID = 23
SELECT @List
This approach to aggregate concatenation is not guaranteed to work. If you are on at least SQL Server 2005 XML PATH
or CLR aggregates are preferred.
The definitive article on the subject is Concatenating Row Values in Transact-SQL
Description
I have done this using COALESCE
in the past too, but i suggest another approach because you dont need a variable. Use the T-SQL
function STUFF
to get this done.
Sample
SELECT STUFF((
select ','+ cast(OfferID as nvarchar(255))
from Emp b
WHERE a.EmpID= b.EmpID
FOR XML PATH('')
),1,1,'') AS COLUMN2
FROM Emp a
GROUP BY a.EmpID
More Information
STUFF (Transact-SQL)
Description
dknaack solution has some drawbacks when text contains some XML caracters like <
, which are html-entitized as <
. This can be solved using the TYPE
directive which sends the response as xml, then extract the raw value
as string.
There are plenty more solutions to the problem in this article : Concatenating Row Values in T-SQL
Sample
SELECT STUFF((
select ','+ cast(OfferID as nvarchar(255))
from Emp b
WHERE a.EmpID= b.EmpID
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
,1,1,'') AS COLUMN2
FROM Emp a
GROUP BY a.EmpID