SQL, How to Concatenate results?

sql sql-server sql-server-2005 concatenation sql-server-group-concat
Darknight picture Darknight · Aug 27, 2009 · Viewed 126.5k times · Source

I currently have a SQL query that returns a number of fields. I need one f the fields to be effectively a sub query sub that.

The Problem in detail:

If I have a table X with two columns, ModuleID and say ModuleValue, how can I write a SQL query to take the results and Concatenate it into one field:

EG Results returned from 

 (SELECT ModuleValue FROM Table_X WHERE ModuleID=@ModuleID)

Value 1

Value 2

Value 3

...

I need to return the result thus (as a single row, unlike the above):

Value 1, Value 2, Value 3

Is there a simple Concatenation method that could be user?

EDIT:

DB is MS TSQL (2005)

Answer

LukeH picture LukeH · Aug 27, 2009

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.)

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More