Menu
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)
702
You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
MySQL CONCAT() Function The CONCAT() function adds two or more expressions together. Note: Also look at the CONCAT_WS() function.
In SQL, you can also concatenate numerical data from the table in the same way as we concatenate strings. The CONCAT function can also be used to join numeric values.
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.)
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
