Blog ala Vidar

What do you do if you got some records of for example users and you want to return them in a comma separated list? Most of you will probably do something like this:

SET NOCOUNT ON

CREATE TABLE MyTable (
	Login NVARCHAR(128)
)

INSERT INTO MyTable
  (Login)
VALUES
  ('Vidar'), ('Johnny'), ('Peter'), ('JanCB'), ('Georgij'), ('Tomas'), ('Torgeir')

DECLARE @Logins AS NVARCHAR(MAX)
DECLARE @Login AS NVARCHAR(128)

SELECT Login
	INTO #MyLogins
	FROM MyTable

WHILE EXISTS (SELECT * FROM #MyLogins)
BEGIN
	SELECT TOP 1
	  @Login = Login
		FROM #MyLogins

	IF @Logins IS NULL
	BEGIN
		SET @Logins = @Login
	END
	ELSE
	BEGIN
		SET @Logins = @Logins + ', ' + @Login
	END

	DELETE
		FROM #MyLogins
		WHERE Login = @Login
END

DROP TABLE #MyLogins
DROP TABLE MyTable

PRINT @Logins

Yes, I know. This is not best practice with creating two tables etc…. but you get my point. Looping through a record set and add a field to a variable. But, why not just do:

SET NOCOUNT ON

CREATE TABLE MyTable (
	Login NVARCHAR(128)
)

INSERT INTO MyTable
  (Login)
VALUES
  ('Vidar'), ('Johnny'), ('Peter'), ('JanCB'), ('Georgij'), ('Tomas'), ('Torgeir')

DECLARE @Logins AS NVARCHAR(MAX) = ''

SELECT @Logins = @Logins + Login + ', '
	FROM MyTable

DROP TABLE MyTable

PRINT SUBSTRING(@Logins, 1, LEN(@Logins) - 1)

This works in SQL Server 2005 too. You just have to SET @Logins = ‘’ after you declare it. And you need to split up the values to be inserted.