Return Rows Into Single Concatenated List

Posted: January 29, 2009 in SQL Server 2005, SQL Server 2005 - XML

To select a result set and return the results in a single concatenated string, open a new query window and execute the following line of code:

-- Create a new test table
CREATE TABLE [MyTest] ([TestValue] VARCHAR(10));

-- Insert test data into the table
INSERT INTO [MyTest] VALUES ('A Test');
INSERT INTO [MyTest] VALUES ('B Test');
INSERT INTO [MyTest] VALUES ('C Test');
INSERT INTO [MyTest] VALUES ('D Test');
INSERT INTO [MyTest] VALUES ('E Test');

-- Declare variables
DECLARE @Result    VARCHAR(MAX);
DECLARE @Space     VARCHAR(1);
DECLARE @Delimiter VARCHAR(1);

-- Initialise varibales
SET @Result    = '';
SET @Space     = '|';
SET @Delimiter = ',';

-- Use XML structure to output values into a single string
SELECT @Result = REPLACE
                 (
                  (SELECT   REPLACE([TestValue], ' ', @Space) AS [data()]
                   FROM     [MyTest]
                   ORDER BY [TestValue]
                   FOR XML PATH ('')
                  ), ' ', @Delimiter
                 );

-- Replace the '|' characters with spaces again
SELECT @Result = REPLACE(@Result, @Space, ' ');

-- Output the result
PRINT '';
PRINT @Result;

-- Drop the table
DROP TABLE MyTest;
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