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)

Subreption

Are you wanting to just retrieve the data or update another field with it? – Voiceful

Just Retrieving, Also its MS TSQL – Subreption

Answer 1

With MSSQL you can do something like this:

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

Bassett

Thanks! I'll give this a go now, will mark you as answered if it works. – Subreption

SELECT LEFT(@result,LEN(@result)-1) AS Txt – Bomke

Answer 2

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

Chemoreceptor

Well noted, in this case it was a varchar, but in other cases this coule well be other value types. – Subreption

This is clever and quite elegant. – Rookie

Answer 3

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.

Bedivere

Related since it was useful for me: #2567500 – Smectic

Answer 4

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

Spurrier

seems interesting, haven't used XML path much. I'll certain play with this one. Thanks! – Subreption

how to remove the last delimma, i mean there is still a useless , at the end – Temporize

Answer 5

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

More at:

https://mcmap.net/q/40515/-how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-server

Matrimony

This is actually peferct – Shirring

Answer 6

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.

Bogie

Answer 7

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

Uzbek

Answer 8

Just pointing out with MS SQL Server you can use CONCAT() to put things together and then just strip the trailing comma. Here are a couple of examples using differing techniques. Note how I used both the Id and Name columns with some other characters in one of these to get (1)Amir, (2)Sofia, (3)Aya, (4)Mateo, (5)Leila, (6)Yara, (7)Ndidi, (8)Santiag just as an example.

DECLARE @Person as TABLE
(
    Id INT PRIMARY KEY,
    Name VARCHAR(255)
);

DECLARE 
    @AllThingsPerson VARCHAR(500) ='',
    @AllThingsPersonX VARCHAR(500) ='';

INSERT INTO @Person(Id, Name)
VALUES
    (1, 'Amir'),
    (2, 'Sofia'),
    (3, 'Aya'),
    (4, 'Mateo'),
    (5, 'Leila'),
    (6, 'Yara'),
    (7, 'Ndidi'),
    (8, 'Santiago');


SELECT @AllThingsPerson = CONCAT(@AllThingsPerson, Name, ', ') 
FROM @Person;
SELECT SUBSTRING(@AllThingsPerson,0, LEN(@AllThingsPerson)-1);  ;

SELECT @AllThingsPersonX =
(
SELECT CONCAT('(',Id,')',Name, ',') AS 'data()'
FROM @Person
FOR XML PATH('')
);

SELECT SUBSTRING(@AllThingsPersonX,0, LEN(@AllThingsPersonX)-1);

Foolish