How to Select a Table Into a String in T-SQL

Formatting the result as a string allows you to move data between disparate systems.

Transact-SQL -- known as T-SQL -- allows you to perform complex manipulations on data to meet your particular business needs. You will need some database programming knowledge to select the data from a table and output the result as a string. Basically, you will select rows of data from the table and insert a delimiter between each element, concatenating the entire thing into a string. You will then output the string as the result of this procedure. This kind of data manipulation is especially useful when you need to retrieve address and contact information from a database.

Things You'll Need

• MS SQL Server
• MS SQL Server Management Studio

Instructions

1. Launch the MS SQL Server Management Studio application, by clicking on “Start,” then “Programs” and choosing “SQL Server Management Studio” from the “SQL Server” menu.

2. Connect to the database from which you will be retrieving data, using the prompts inside the SQL Server Management Studio. You can usually connect to a previously configured database by clicking on the “Connect” icon within the application.

3. Launch a new query editor window by clicking on the “SQL” icon.

4. Declare a variable in which you will place your result string. You will need to type the following into the query editor:
   DECLARE @ResultString nvarchar(max)
   SET @ResultString =’’

5. Type the following code into the query editor to select the data from the table, insert a comma between each element as a delimiter, concatenate the data and return the result string:

   SELECT
   @ResultSring = @ResultString +
   CASE @ResultString WHEN ‘’ THEN ‘’ ELSE ‘,’ END +
   Field1 + Field2 + … Fieldn
   FROM
   TableName

   Replace the values in “Field1 + Field2 + … Fieldn” with your field names and the “TableName” with the name of your table.

6. Run the query and wait for the result to display on the screen.

Tips & Warnings

• Convert any numeric fields in the “SELECT” statement to strings using the CAST(Field as NVARCHAR(MAX)) command.

• Convert any NULL values in alphanumeric fields in the “SELECT” statement to strings using the ISNULL(Field,’’) command.

• You can use the COALESCE() function to return strings containing the first non-null value in a row.

References

• Dobrzanski.net: T-SQL – How to select multiple rows into a single row; Jarosław Dobrzańsk;2009
• Michael A. Rife's Blog: Table Rows to One String in SQL ;Michael Rife;2007
• CodeSnip.net: SELECT column values into a concatenated string (SQL);2008

Resources

• Tech Republic: Creative uses for COALESCE() in SQL Server;Arthur Fuller;2007