SQL, How to Concatenate results?
Solution 1
With MSSQL you can do something like this:
declare @result varchar(500)
set @result = ''
select @result = @result + ModuleValue + ', '
from TableX where ModuleId = @ModuleId
Solution 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
.)
Solution 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.
Solution 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
Solution 5
In my opinion, if you are using SQL Server 2017 or later, using STRING_AGG( ... )
is the best solution:
More at:
Darknight
Electronic Engineer (DEng) | Mechanical Engineer (BEng) | Computer Science (MSc) | Commercial Software developer (Senior C# Developer) | Junior Mixed Martial Artist | Indie Filmmaker |3D artist | Fav quote: "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds castles in the air, from air, creating by exertion of the imagination." -- Fred Brooks Skills: C#.NET (LINQ, 4.0), VB.NET, ASP.NET, Python, F# (Basic), WPF (Basic), SilverLight(Basic) PHP (very basic), IIS 6 & 7 (Intermediate), FORTRAN, VB6, SQL 2005 & 2008, (X)HTML,CSS,Javascript SGML, XML, XSLT, jQuery, HASKELL (Basic), SubVersion, LaTeX, AutoCAD, NX IDEAS 10, Processing 1.0, Clemintines, Knowledge Studio, x86 & Z80 Assembly, 3DS Max 2010, AudoDesk Combustion, Adobe After Effects CS3, SynthEyes, PFTrack, MATLAB, GNUPlot, Dot Language (GraphViz), Sony Vegas 7, Open Concept Judo, Wing Tsun (Orange Belt)
Updated on July 09, 2022Comments
-
Darknight almost 2 years
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)
-
Evernoob almost 15 yearsAre you wanting to just retrieve the data or update another field with it?
-
Darknight almost 15 yearsJust Retrieving, Also its MS TSQL
-
-
Darknight almost 15 yearsThanks! I'll give this a go now, will mark you as answered if it works.
-
Darknight almost 15 yearsseems interesting, haven't used XML path much. I'll certain play with this one. Thanks!
-
Darknight almost 15 yearsThis is what I was looking for worked just fine, many thanks!
-
Darknight almost 15 yearsWell noted, in this case it was a varchar, but in other cases this coule well be other value types.
-
suiwenfeng over 8 yearsSELECT LEFT(@result,LEN(@result)-1) AS Txt
-
starryknight64 over 7 yearsRelated since it was useful for me: stackoverflow.com/questions/2567000/…
-
yekanchi over 5 yearshow to remove the last delimma, i mean there is still a useless
,
at the end -
Jose Jet over 5 yearsThis is actually peferct
-
Wayne Conrad almost 4 yearsThis is clever and quite elegant.