SQL select row into a string variable without knowing columns

Question

Hello,

I am new to writing SQL and would greatly appreciate help on this problem. :)

I am trying to select an entire row into a string, preferably separated by a space or a comma. I would like to accomplish this in a generic way, without having to know specifics about the columns in the tables.

What I would love to do is this:

DECLARE @MyStringVar NVARCHAR(MAX) = ''
@MyStringVar = SELECT * FROM MyTable WHERE ID = @ID AS STRING

But what I ended up doing was this:

DECLARE @MyStringVar = ''
DECLARE @SecificField1 INT
DECLARE @SpecificField2 NVARCHAR(255)
DECLARE @SpecificField3 NVARCHAR(1000)
...
SELECT @SpecificField1 = Field1, @SpecificField2 = Field2, @SpecificField3 = Field3 FROM MyTable WHERE ID = @ID
SELECT @StringBuilder = @StringBuilder + CONVERT(nvarchar(10), @Field1) + ' ' +  @Field2 + ' ' + @Field3

Yuck. :(

I have seen some people post stuff about the COALESCE function, but again, I haven't seen anyone use it without specific column names.

Also, I was thinking, perhaps there is a way to use the column names dynamically getting them by:

SELECT [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable' 

It really doesn't seem like this should be so complicated. :(

What I did works for now, but thanks ahead of time to anyone who can point me to a better solution. :)

EDIT: Got it fixed, thanks to everyone who answered. :)

Answer 1

The short answer is you cannot do that.

The only thing you can do is similiar to what you have.

SELECT @MyField1 = MyField1, @MyField2= MyField2...etc FROM MyTable

And then you can concat these fields:

SELECT @MyResult = (SELECT @MyField1 + @MyField2 + MyField3...)

SELECT @MyResult

Answer 2

Something like this might work, if you can handle XML formatted output:

DECLARE @MyStringVar NVARCHAR(MAX) = ''
SET @MyStringVar = (SELECT * FROM MyTable WHERE ID = @ID FOR XML RAW)

SELECT @MyStringVar 

As an extra bonus, if your data is amenable to it, you can strip away the XML formatting with this, instead of the SELECT @MyStringVar:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(@MyStringVar, '<row ', ''), '="', ' '), '" ', ', '), '"/>', '')

If your data includes any of the characters that get used in the tags, of course you'll have to do some extra conversion, but this should get you pretty close to what you want.

---

Just noticed that it looks like you don't actually want the column names to display as well as the data. In that case one of the information_schema queries in the other answers would be the best way to get what you want.

Answer 3

To do it dynamically, just knowing the table name, you can use this approach:

DECLARE @nSQL NVARCHAR(MAX)
SELECT @nSQL = COALESCE(
     @nSQL + ' + '','' + CAST(ISNULL([' + c.COLUMN_NAME + '], '''') AS NVARCHAR(MAX))', 
    'CAST([' + c.COLUMN_NAME + '] AS NVARCHAR(MAX))')
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'TestTable'

SET @nSQL = 'SELECT ' + @nSQL + ' FROM YourTable'

EXECUTE sp_executesql @nSQL

It depends what your end goal is though.

This will handle null values in the columns, but (e.g.) NULL integers come out as 0 for example.

Answer 4

give this a try:

DECLARE @SQL nvarchar(MAX), @YourTable nvarchar(200)
SET @YourTable='YourTableName'
SELECT @SQL=
    STUFF(
             (
                  SELECT
                  ' + '','' + COALESCE(''''''''+CONVERT(NVARCHAR(MAX),['+c.COLUMN_NAME+']'+CASE WHEN c.DATA_TYPE='datetime' THEN ',121' ELSE '' END+')+'''''''',''null'')'
                      FROM INFORMATION_SCHEMA.COLUMNS c
                      WHERE c.TABLE_NAME = 'ap_voucher'
                      ORDER BY ORDINAL_POSITION
                      FOR XML PATH('')
             ), 1, 9, ''
         )
SET @SQL = 'SELECT ' + @SQL + ' FROM '+@YourTable
exec (@SQL)

sample output:

---------------------------------------------------------------------------
'030',null,'I','Zzz0',null,'1000021111          ','2000-03-01 00:00:00.000'
'001',null,'I','zzz0',null,'900099618           ','1999-12-03 00:00:00.000'
'001',null,'I','ET10',null,'KOU557              ','1999-11-01 00:00:00.000'
'001',null,'I','Mzzz',null,'017288              ','1999-11-12 00:00:00.000'

Answer 5

If the goal is change tracking, then I would recommend against using concatenation as suggested by KM.

1. The order of the columns must remain unchanged. If the column order changes even slightly, the meaning of the values will be vague at best or more likely lost. It is recommended that you never rely on the column order.

2. As a table gets wider and/or contains lots of data, the concatenation will get slower. SQL Server is not know for having efficient concatenation. Further, imagine a table with a couple of dozen nvarchar(max) fields with say 2000 characters each. That will produce a string that is on the order of 48K!

3. You may need to change the delimiter. Suppose I have a value equal "'Pain','Suffering' and 'Trouble'". This type of entry will create problems when you go to analyze the data.

If you are using SQL Server 2008, I would recommend using SQL Server's change tracking mechanism. If you are not using that, then I would recommend purchasing a third-party auditing tool. If that is also not an option, then I would write the deleted value into another table using a trigger. That, at least, forces you to ensure that the auditing data schema is synchronized with the source schema.