There are many ways to do this in SQL Server,Here i will show you the one of the way to Convert a column of the table into Comma separated string using FOR XML PATH in query.
Example:
CREATE TABLE rowconcat
(
rowno INT PRIMARY KEY,
rowcode VARCHAR(30))
INSERT INTO rowconcat VALUES (1,’one’)
INSERT INTO rowconcat VALUES (2,’two’)
INSERT INTO rowconcat VALUES (3,’three’)
INSERT INTO rowconcat VALUES (4,’four’)
INSERT INTO rowconcat VALUES (5,’five’)
SELECT STUFF(
(SELECT ‘,’+rowcode FROM rowconcat FOR XML PATH(”)),
1,1,”);
Output:
click on image to get better view
Why not just use the COALESCE function?
Hi Dave,
Using COALESCE Function also we can achieve the result,But while using COALESCE Function you have declare the variable and assign that to the result and then get the output.But what i post here is the simple and with in single select statement we can achieve the result.
Here, is the way to get the output using COALESCE:
DECLARE @rowcode varchar(100)
SELECT @rowcode = COALESCE(@rowcode + ‘,’, ”) +
CAST(rowcode AS varchar(5))
FROM rowconcat
SELECT @rowcode
Thanks and keep on your visit…