Fun With SET Based Operations 2

Okay, now for a little more fun with SET based operations. This is a quick post that will show you how to combine multiple rows for 1 column, into a 1 row, 1 column, as a comma delimited list. It’s a very simple piece of code and the way that it works is exactly the same as my previous post. SET based operations are not transactional based; and that’s why we can do these things.

First, let’s create a new temporary table with some rows to play with.

SET NOCOUNT OFF
GO

--Create our test table
CREATE TABLE #TestTable
(
TestColumn VARCHAR(100) NOT NULL
);

DECLARE @intCount INT
DECLARE @chvString VARCHAR(100)

SET @intCount = 0

--Insert some test data
WHILE @intCount <= 30
BEGIN
	SET @chvString = 'Test string number ' + RIGHT('0' + CAST(@intCount AS VARCHAR(2)),2)

	INSERT INTO #TestTable
	(TestColumn)
	VALUES(@chvString)

	SET @intCount = @intCount + 1
END

--This is how our data looks in the table
SELECT * FROM #TestTable

Now that we have our table, I’ll show you how to create a comma delimited list in a select statement. The way it works is very simple, although the SELECT statement will normally return a data set with 1 or more rows; we’re going to combine it with a variable, which we will use to concatenate the rows together.

--This is how our data looks in the table
SELECT * FROM #TestTable

DECLARE @chvCommaDelimitedList VARCHAR(MAX)
SET @chvCommaDelimitedList = ''

SELECT
	@chvCommaDelimitedList = @chvCommaDelimitedList + ', ' + TestColumn
FROM
	#TestTable

--Let's see our new comma delimited list
SELECT @chvCommaDelimitedList

As you can see from the SELECT, it’s not quite perfect, we have to trim off the leading comma to make it useful to us, but that’s not exactly hard to do.