Concatenating multiple rows into one column in T-SQL

This post looks at how to deliminate many rows into one single column.

I come across this issue quite often so I thought I would share my solution. For example it's quite a good solution for quick ad-hoc data extracts.

There are many ways to do this but the most performant way is actually the simplest too.

The most common approach which most people fall back to would be to iterate through the collection using a Cursor and format a string that way. For many reasons this is not a good idea, from memory to potential deadlock issues to name 2.

For this demo im going to comma deliminate all my tables into a single column for me to then copy this value straight into my to then save as a CSV.

My data looks like the following.

ProductReview
StoreEventPage
AuditLog
Author
TimeLine
TimeLineEvent

Now using the gist below. I can combine the result set of these string values and concatinate them to one string variable.

Now again this is commonly done but note the COALESCE expression. This avoids having duplicated commas and removes null values. The COALESCE expression is a syntactic shortcut for the CASE expression, when the first argument is NULL, COALESCE returns NULL.

The end result is the following which we can now save as a csv.

ProductReview,StoreEventPage,AuditLog,Author,TimeLine,TimeLineEvent

Thanks for reading. Always appreciate hearing your thoughts and comments.
Checkout my website