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.
DECLARE @DATA NVARCHAR(MAX)
SET @DATA = ”
SELECT @DATA = @DATA + ‘Data to be concatenated’
SELECT @DATA AS DATA