T SQL Convert a row to a string

Question

Is there a valid way in SQL Server 2005 to read a row of data into a string?

For example if my table looked like the following:

ID | First Name | Last Name | Color
-----------------------------------
 1 | Timmy      | Jones     | pink
 2 | Martha     | Fisher    | green

That I could get back as a result:

'1 Timmy Jones pink'
'2 Martha Fisher green'

Preferably I could get back with the column name appended such as:

'ID: 1, First Name: Timmy, Last Name: Jones, Color: pink'
'ID: 2, First Name: Martha, Last Name: Fisher, Color: green'

Update:

I'm looking for a dynamic solution so I guess querying against INFORMATION_SCHEMA would be more appropriate.

This actually helps a bit because I'm looking to set it off from an update trigger, so I'll have one row at a time and I can query against the INFORMATION_SCHEMA for the main table checking datatypes to eliminate text, ntext and image that the generated tables, 'inserted' and 'deleted' choke on.

Solution: (updated: had to add some more code to return the one value)

-- passed in table name & schema
DECLARE @table_name nvarchar(100)
DECLARE @schema_name nvarchar(100)

-- set initial value
SET @table_name = 'my_table'
SET @schema_name = 'dbo'

-- Variable to hold query
DECLARE @sql nvarchar(MAX)
SET @sql = 'SELECT @row_stringOUT = '

-- Query columns meeting criteria and build query
SELECT
    -- Query as one string
    @sql = (@sql + '  ''  ' + COLUMN_NAME + ': '' + CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN '''' WHEN ISDATE(' + COLUMN_NAME + ') = 1 THEN CONVERT(nvarchar(100), ' + COLUMN_NAME + ', 120) ELSE CAST(' + COLUMN_NAME + ' AS nvarchar(100)) END + ')
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    table_schema = @schema_name
    AND
    table_name = @table_name
    AND
    -- Filter out unuseable data types
    data_type NOT IN ('text', 'ntext', 'image')

-- Trim extra character before FROM statement then add FROM statement
SELECT @sql = LEFT(@sql, LEN(@sql) - 1) + ' FROM ' + @table_name;

-- Execute Query
DECLARE @params nvarchar(MAX)
SET @params = N'@row_stringOUT ntext OUTPUT';

DECLARE @ret_value nvarchar(MAX)

EXEC sp_executesql
    @sql
    , @params
    , @row_stringOUT = @ret_value OUTPUT

SELECT @ret_value

Answer 1

Select CAST(ID As Varchar(8)) + ' ' + [First Name] + ' ' + [Last Name] + ' ' + Color From TheTable

To get the verbose formatting you mentioned:

Select 'ID: ' + CAST(ID As Varchar(8)) + ', First Name: ' + [First Name] + ', Last Name: ' + [Last Name] + ', Color: ' + Color From TheTable

Edit: Now assuming ID is an integer autonumber. Most likely the case, as ajdams points out.

Answer 2

you can just concat the columns. if you want to get column names to you'll have to use some mix of dynamic sql, INFORMATION_SCHEMA views and querying...

SELECT  cast(ID as varchar(10)) + ' ' + [First Name] + ' ' + [Last Name] + ' ' + Color 
FROM    MyTable

Answer 3

Actually if ID has a data type of int the solution Patrick suggested will give you a conversion error. You will have to do something like this

SELECT CAST(ID AS varchar(10)) + ' ' + [first name] + ' ' + [last name + ' ' + color
FROM MYTABLE