T-SQL Alternative To Using Cursors


Today I found myself writing a T-SQL Cursor statement to condense multiple rows in a table to a single row of data in which the data column was a comma separated key/value list representing each of the columns in the source table, and each row of data was separated by a pipe ‘|’.

The reason behind this is that the data was to be transformed from a normalised table in one database for transference to a new table in a different database.  The new table in the new database has one row that represents the source table in the source database.  The new table consists of 3 fields Name AS NVARCHAR(256) NOT NULL, Value AS NVARCHAR(256) NOT NULL DEFAULT N”, and Data AS NVARCHAR(MAX).  The Name and Value columns provide the compound primary key.

I started off by writing a cursor statement, but I ran into problems.  So I did a bit of Googling and came across the article Using T-SQL to Concatenate Data Into a Variable published in SQL Server Pro June 25, 2002 by Brian Moran.

This article provided a very simple SQL statement that negates the need to use T-SQL cursors, and it is fast for my purposes.  The T-SQL statement below is my modified version for doing string concatenation.


USE [DatabaseName]
GO

DECLARE @DATA NVARCHAR(MAX)
SET @DATA = ”
SELECT @DATA = @DATA + ‘Data to be concatenated’
FROM [DatabaseTable]
SELECT @DATA AS DATA

Advertisements