Reputation: 2500
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)
Upvotes: 49
Views: 165444
Reputation: 34227
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);
Upvotes: 0
Reputation: 570
In my opinion, if you are using SQL Server 2017 or later, using STRING_AGG( ... )
is the best solution:
More at:
Upvotes: 12
Reputation: 4957
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, '' )
Upvotes: 6
Reputation: 269628
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
.)
Upvotes: 40
Reputation: 755361
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
Upvotes: 16
Reputation: 8029
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.
Upvotes: 6
Reputation: 580
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.
Upvotes: 19
Reputation: 103535
With MSSQL you can do something like this:
declare @result varchar(500)
set @result = ''
select @result = @result + ModuleValue + ', '
from TableX where ModuleId = @ModuleId
Upvotes: 40