CODEWITHSUNDEEP
sqlt-sql
Alex Dybenko
Alex Dybenko

Reputation: 510

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

i need to put all, not null, values of single row into one string, 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!

Upvotes: 3

Views: 1127

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

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)

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

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)

Upvotes: 4

x2.
x2.

Reputation: 9668

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

And maybe you need to cast some values to varchar

Upvotes: 1

Related Questions