If only I were

Building Great Software

SQL Server COALESCE

leave a comment »

Two days I needed to take a block of records and turn the rows into a horizontal comma separated list. That comma separated list in turn needed to be the value for a column in a larger query.

I googled and found quite a few potential solutions and finally settled on using the COALESCE function. You certainly could use temp tables or cursors accomplish this. But the COALESCE function, with help from Arian and a bit of my tweaking ended up being a very succinct and easy to read solution.

DECLARE @list NVARCHAR(MAX)
SELECT @list = COALESCE(@list+’, ‘,”)+[FirstName] +’ ‘+[LastName]
FROM [UserTable]
SELECT @list

Of course there were some joins I needed to do in the FROM clause to get the correct data and it was also parameterized, but this was the gist of it.

I pushed the above snippet into a function, changed the last SELECT statement to a RETURN statement(for the sake of the function) and then used that function in the larger query to show the list of people as a comma separated list. It worked just as intended and solved the issue at hand.

Advertisements

Written by Chris Sutton

September 25, 2007 at 7:44 am

Posted in Technology

Tagged with

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: