sql server - SQL, How to Concatenate results?

ID : 131344

viewed : 7

Tags : sqlsql-serversql-server-2005concatenationsql-server-group-concatsql

Top 5 Answer for sql server - SQL, How to Concatenate results?

vote vote

94

This one automatically excludes the trailing comma, unlike most of the other answers.

DECLARE @csv VARCHAR(1000)  SELECT @csv = COALESCE(@csv + ',', '') + ModuleValue FROM Table_X WHERE ModuleID = @ModuleID 

(If the ModuleValue column isn't already a string type then you might need to cast it to a VARCHAR.)

vote vote

90

With MSSQL you can do something like this:

declare @result varchar(500) set @result = '' select @result = @result + ModuleValue + ', '  from TableX where ModuleId = @ModuleId 
vote vote

73

In mysql you'd use the following function:

SELECT GROUP_CONCAT(ModuleValue, ",") FROM Table_X WHERE ModuleID=@ModuleID 

I am not sure which dialect you are using.

vote vote

68

In SQL Server 2005 and up, you could do something like this:

SELECT      (SELECT ModuleValue + ','      FROM dbo.Modules      FOR XML PATH('')     )  FROM dbo.Modules WHERE ModuleID = 1 

This should give you something like what you're looking for.

Marc

vote vote

59

In my opinion, if you are using SQL Server 2017 or later, using STRING_AGG( ... ) is the best solution:

More at:

https://stackoverflow.com/a/42778050/1260488

Top 3 video Explaining sql server - SQL, How to Concatenate results?

Related QUESTION?