SQL, How to Concatenate results?

Question

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)

Are you wanting to just retrieve the data or update another field with it? — Evernoob, Aug 27 '09 at 09:02

LukeH · Answer 1 · 2009-08-27T09:33:18.797

38

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

edited Aug 27 '09 at 09:33

answered Aug 27 '09 at 09:18

LukeH

252,910
55
358
405

Well noted, in this case it was a varchar, but in other cases this coule well be other value types. – Darknight Aug 27 '09 at 09:26
This is clever and quite elegant. – Wayne Conrad Aug 02 '20 at 22:21

score 37 · Accepted Answer · answered Aug 27 '09 at 09:01

37

With MSSQL you can do something like this:

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

answered Aug 27 '09 at 09:01

Blorgbeard

97,316
47
222
267

Thanks! I'll give this a go now, will mark you as answered if it works. – Darknight Aug 27 '09 at 09:03
This is what I was looking for worked just fine, many thanks! – Darknight Aug 27 '09 at 09:24
4

SELECT LEFT(@result,LEN(@result)-1) AS Txt – suiwenfeng Jan 13 '16 at 05:20

score 18 · Answer 3 · answered Aug 27 '09 at 09:01

18

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.

answered Aug 27 '09 at 09:01

Silvan Mühlemann

528
6
14

1

Related since it was useful for me: http://stackoverflow.com/questions/2567000/mysql-and-group-concat-maximum-length – starryknight64 Mar 20 '17 at 16:25

score 12 · Answer 4 · answered Aug 27 '09 at 09:05

12

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

answered Aug 27 '09 at 09:05

marc_s

704,970
168
1,303
1,425

seems interesting, haven't used XML path much. I'll certain play with this one. Thanks! – Darknight Aug 27 '09 at 09:23
how to remove the last delimma, i mean there is still a useless `,` at the end – yekanchi Dec 01 '18 at 11:44

score 8 · Answer 5 · edited Mar 26 '20 at 07:18

8

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

edited Mar 26 '20 at 07:18

Jon R

707
9
9

answered Sep 08 '17 at 17:04

sdsc81

550
8
17

This is actually peferct – Jose Jet Feb 21 '19 at 10:17

score 6 · Answer 6 · answered Aug 27 '09 at 09:02

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.

score 4 · Answer 7 · answered Apr 26 '16 at 10:12

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, '' )

SQL, How to Concatenate results?

7 Answers7

Linked

Related