Convert SQL Server result set into string
SqlSql ServerSql Server-2005TsqlSql Problem Overview
I am getting the result in SQL Server as
SELECT StudentId FROM Student WHERE condition = xyz
I am getting the output like
StudentId 1236The output parameter of the stored procedure is7656
8990 ........
@studentId string and I want the return statement as
1236, 7656, 8990.
How can I convert the output in the single string?
I am returning single column [ie. StudentId]
Sql Solutions
Solution 1 - Sql
Test this:
DECLARE @result NVARCHAR(MAX)
SELECT @result = STUFF(
( SELECT ',' + CONVERT(NVARCHAR(20), StudentId)
FROM Student
WHERE condition = abc
FOR xml path('')
)
, 1
, 1
, '')
Solution 2 - Sql
DECLARE @result varchar(1000)
SELECT @result = ISNULL(@result, '') + StudentId + ',' FROM Student WHERE condition = xyz
select substring(@result, 0, len(@result) - 1) --trim extra "," at end
Solution 3 - Sql
Use the COALESCE function:
DECLARE @StudentID VARCHAR(1000)
SELECT @StudentID = COALESCE(@StudentID + ',', '') + StudentID
FROM Student
WHERE StudentID IS NOT NULL and Condition='XYZ'
select @StudentID
Solution 4 - Sql
Both answers are valid, but don't forget to initializate the value of the variable, by default is NULL and with T-SQL:
NULL + "Any text" => NULL
It's a very common mistake, don't forget it!
Also is good idea to use ISNULL function:
SELECT @result = @result + ISNULL(StudentId + ',', '') FROM Student
Solution 5 - Sql
Use the CONCAT function to avoid conversion errors:
DECLARE @StudentID VARCHAR(1000)
SELECT @StudentID = CONCAT(COALESCE(@StudentID + ',', ''), StudentID)
FROM Student
WHERE StudentID IS NOT NULL and Condition='XYZ'
select @StudentID
Solution 6 - Sql
This one works with NULL Values in Table and doesn't require substring operation at the end. COALESCE is not really well working with NULL values in table (if they will be there).
DECLARE @results VARCHAR(1000) = ''
SELECT @results = @results +
ISNULL(CASE WHEN LEN(@results) = 0 THEN '' ELSE ',' END + [StudentId], '')
FROM Student WHERE condition = xyz
select @results
Solution 7 - Sql
The answer from brad.v is incorrect! It won't give you a concatenated string.
Here's the correct code, almost like brad.v's but with one important change:
DECLARE @results VarChar(1000)
SELECT @results = CASE
WHEN @results IS NULL THEN CONVERT( VarChar(20), [StudentId])
ELSE @results + ', ' + CONVERT( VarChar(20), [StudentId])
END
FROM Student WHERE condition = abc;
See the difference? :) brad.v please fix your answer, I can't do anything to correct it or comment on it 'cause my reputation here is zero. I guess I can remove mine after you fix yours. Thanks!
Solution 8 - Sql
The following is a solution for MySQL (not SQL Server), i couldn't easily find a solution to this on stackoverflow for mysql, so i figured maybe this could help someone...
ref: https://forums.mysql.com/read.php?10,285268,285286#msg-285286
original query...
SELECT StudentId FROM Student WHERE condition = xyz
original result set...
StudentId
1236
7656
8990
new query w/ concat...
SELECT group_concat(concat_ws(',', StudentId) separator '; ')
FROM Student
WHERE condition = xyz
concat string result set...
StudentId
1236; 7656; 8990
note: change the 'separator' to whatever you would like
GLHF!
Solution 9 - Sql
or a single select statement...
DECLARE @results VarChar(1000)
SELECT @results = CASE
WHEN @results IS NULL THEN CONVERT( VarChar(20), [StudentId])
ELSE ', ' + CONVERT( VarChar(20), [StudentId])
END
FROM Student WHERE condition = abc;
Solution 10 - Sql
Assign a value when declaring the variable.
DECLARE @result VARCHAR(1000) ='';
SELECT @result = CAST(StudentId AS VARCHAR) + ',' FROM Student WHERE condition = xyz
Solution 11 - Sql
Use STRING_AGG:
SELECT STRING_AGG(sub.StudentId, ',') FROM
(select * from dbo.Students where Name = 'Test3') as sub
If you want to use e.g ORDER BY:
SELECT STRING_AGG(sub.StudentId, ',') WITHIN GROUP(Order by StudentId) FROM
(select * from dbo.Students where Name = 'Test3') as sub