SQL, How to Concatenate results?

0 votes
asked Aug 27, 2009 by darknight

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)

6 Answers

0 votes
answered Aug 27, 2009 by blorgbeard

With MSSQL you can do something like this:

declare @result varchar(500)
set @result = ''
select @result = @result + ModuleValue + ', ' 
from TableX where ModuleId = @ModuleId
0 votes
answered Aug 27, 2009 by idrosid

It depends on the database you are using. MySQL for example supports the (non-standard) group_concat function. So you could write:

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

Group-concat is not available at all database servers though.

0 votes
answered Aug 27, 2009 by marc-s

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

0 votes
answered Aug 27, 2009 by lukeh

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

0 votes
answered Aug 26, 2016 by sandeep-rawat

Small update on Marc we will have additional " , " at the end. i used stuff function to remove extra semicolon .

       SELECT STUFF((  SELECT ',' + ModuleValue AS ModuleValue
                           FROM ModuleValue WHERE ModuleID=@ModuleID
                      FOR XML PATH('') 
                     ), 1, 1, '' )
0 votes
answered Sep 15, 2017 by sdsc81

In my opinion, using STRING_AGG( ... ) is the best solution:

More at:

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

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...