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

User · Accepted Answer

With MSSQL you can do something like this   declare  result varchar 500  set  result      select  result    result   ModuleValue         from TableX where ModuleId    ModuleId

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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] SQL, How to Concatenate results?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to concatenation

Examples related to sql-server-group-concat