T-SQL – Concatenate row values in Transact-SQL
Here is very good blog about how to concatenate row values in Transact-SQL.
I recently needed to create a report for mainframe directors to show all the mainframe assets each director is linked to.
When the number of items is small and known beforehand, the most common approach is to use group by, case statement and max function.
When the number of items is unknown, concatenating row values can be very tricky. Fortunately, TSQL has provided some very elegant ways developers can use. Using Recursive CTE is one way. But my favorite is to use the FOR XML clause with PATH mode, which is available even in SQL 2005.
In this example, #temp table contains DirectorID, and DeviceName that each Director is linked to. One Director can be linked to several mainframe assets.
The above query will show one row per DirectorID, with each mainframe asset device name being concatenated together and separated by a comma.