-- 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;