Problem: I have a select statement that returns more than one rows of result, and I need to concatenate all rows into one. For example, if I have a table called ‘Game’ and it returns the following result when you select all and you just want to get a list of games’ names:
|1||Rayman: Raving Rabbids|
|2||Naruto: Rise of a Ninja|
|3||Guitar Hero III: Legends of Rock|
|4||Dynasty Warriors 6|
Solution: usually I would thought of to loop through it or in more complex cases, to use a cursor. However, I found a really easy way today (you don’t need to declare the variable asNVARCHAR(MAX), just in case if you have heaps of games and happen to use SQL Server 2005 or above).
DECLARE @games NVARCHAR(MAX)
SELECT @games=@games + Name +', ' AS GameList FROM Games
|Rayman: Raving Rabbids, Naruto: Rise of a Ninja,Guitar Hero III: Legends of Rock,Dynasty Warriors 6,…|
Of course you’ll need to get rid of the last ‘,’ from the variable at the end. How simple, and how could I never knew this before!