I came across a situation today working on a new piece of software where I needed some data from T-SQL. The product is a VB.NET modernisation of existing VB6 source code that accesses a SQL Server 2008 R2 database. The database for the VB6 application uses multiple tables with multiple rows for various resources. As for the VB.NET software, this uses a single table for resources. The columns in the new database’s resources table is Name, Value, and Data. These columns are all strings and Name, Value is a compound primary key.
The problem I had to solve was how do I concatenate each column of each row into a single comma separated string of key value pairs, and then concatenate each string of each row separated by a pipe ‘|’ to form a single pipe separated string. The final format of the string should be: k1=v1,k2=v2,k3=v3|k4=v4,k5=v5,k6=v6|.
I started using cursors to achieve my goal, but for some reason that I could not figure out I could not get a variable to update. So after some searching, I found that the method I was using was the one used for SQL Server 2005 and earlier, and that there is a new way of concatenating strings in later versions of SQL Server.
To achieve the above use the T-SQL STUFF command.
INSERT INTO [Database].[Schema].[Table]
VALUES('Name', 'Value', (SELECT STUFF((
' k1=' + v1 + ',k2=' + v2 + ',k3=' + v3 + ',k4=' + k4 + ',k5=' + k5 + ',k6=' + k6 + '|'
FOR XML PATH ('')),1,1,'')))
In the above code k stands for the field name, and v is the data for that column. And that’s all there is to concatenating all the columns together, and then concatenating each row to produce a single line of data. The good thing about this statement is that you can select from one database table, and insert directly into a table in a different database.