Rows to string – 2 ways..

Standard

Alrighty then, new post time, more so to keep this in a place I can find it because there are a number of places that you can find how to do this, but…

A work colleague and good friend was asking for ways to convert rows to a string, there are at very least two ways to do this:

1: Using FOR XML PATH

DECLARE @Tbl TABLE
(
Name VARCHAR(20)
);

INSERT INTO @Tbl VALUES
('Jim'),
(
'Tim'),
(
'Kim');

DECLARE @ReturnVar VARCHAR(256);

SELECT STUFF((SELECT ',' + Name
                          
FROM @Tbl
                          
FOR XML PATH('')),1,1,'') AS Name;

 

2: Using COALESCE

DECLARE @Tbl TABLE
(
Name VARCHAR(20)
);

INSERT INTO @Tbl VALUES
('Jim'),
(
'Tim'),
(
'Kim');

DECLARE @ReturnVar VARCHAR(256);

SELECT @ReturnVar = COALESCE(@ReturnVar + ', ', '') + Name
FROM @Tbl;

SELECT @ReturnVar;

Pretty simple huh?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s