TSQL: Values of all field in a row into one string

i need to put all, not null, values of single row into one s开发者_StackOverflowtring, like

table:

CustomerName  Address Zip
Alex          Moscow  1234

to result:

CustomerName: Alex
Address: Moscow
Zip: 1234

Important note - i don't know field names/types, so it should go through all fields and all not null values add to list.

Looks like it can do this using xquery, but can't find right syntax. Any hints?

Thanks!

select T2.N.value('local-name(.)', 'nvarchar(128)')+': '+
       T2.N.value('.', 'nvarchar(max)')
from (select *
      from YourTable
      for xml path(''), type) as T1(X)
  cross apply T1.X.nodes('/*') as T2(N)

select 'CustomerName: ' + isNull(CustometName, '') + 'Address: ' 
+ isNull(Address, ''), + 'Zip:' + isNull(Zip, '') from [TableName]

And maybe you need to cast some values to varchar

Not as elegant as Mikael's solution. but i still want to include it.

DECLARE @yourtable nvarchar(128)
DECLARE @sql as nvarchar(2100)
DECLARE @col as nvarchar(2000)

SET @yourtable = '<tablename>'

SELECT @col = coalesce(@col, '' ) + '+'''+t2.column_name+': '' + cast([' + t2.column_name + '] as varchar) + char(32)'
FROM INFORMATION_SCHEMA.TABLES t1 join 
INFORMATION_SCHEMA.COLUMNS t2 on t1.table_name = t2.table_name
where t2.is_nullable = 'NO' and t1.table_name = @yourtable
and t1.table_type = 'BASE TABLE' and t1.table_schema = t2.table_schema
and t2.table_schema = 'dbo'
SET  @sql = 'select ' + stuff(@col, 1,1,'') +' from ' + @yourtable
EXEC (@sql)