Return table row as string in SQL Server


You can return table rows as single row using a COALESCEĀ trick. This can be possible by appending each row value to a variable.

The SQL Coalesce are used to handle NULL values. During the expression evaluation process the NULL values are replaced with the user-defined value.

declare @ret varchar(4000)
select @ret=coalesce(@ret+', ','') + l_name from accounts
print(@ret)

Here in my SQL script, created a variable and append all row values in the column of l_name to sql vaiable @ret and then I print the result

Even though the script can return lengthy string, it may depends on the number of rows and the capacity of varchar variable declared.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.