CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Zerotoinfinity
Zerotoinfinity

Reputation: 6540

Convert SQL Server result set into string

I am getting the result in SQL Server as 

SELECT StudentId FROM Student WHERE condition = xyz

I am getting the output like

StudentId
1236

7656

8990
........

The output parameter of the stored procedure is @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]

Upvotes: 63

Views: 214679

Answers (11)

Przemysław Kleszcz
Przemysław Kleszcz

Reputation: 646

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

Upvotes: 2

Delmirio Segura
Delmirio Segura

Reputation: 1691

Assign a value when declaring the variable.

DECLARE @result VARCHAR(1000) ='';

SELECT @result = CAST(StudentId AS VARCHAR) + ',' FROM Student WHERE condition = xyz

Upvotes: 0

greenhouse
greenhouse

Reputation: 1281

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!

Upvotes: 3

Bonshington
Bonshington

Reputation: 4032

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

Upvotes: 67

Vadym Voznyuk
Vadym Voznyuk

Reputation: 21

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!

Upvotes: 1

Alex
Alex

Reputation: 822

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

Upvotes: 8

pistol-pete
pistol-pete

Reputation: 1261

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

Upvotes: 6

Gega Kakabadze
Gega Kakabadze

Reputation: 11

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

Upvotes: 1

Saloni
Saloni

Reputation: 129

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

Upvotes: 12

ABI
ABI

Reputation: 1754

Test this:

 DECLARE @result NVARCHAR(MAX)

 SELECT @result = STUFF(
                        (   SELECT ',' + CONVERT(NVARCHAR(20), StudentId) 
                            FROM Student 
                            WHERE condition = abc 
                            FOR xml path('')
                        )
                        , 1
                        , 1
                        , '')

Upvotes: 81

brad.v
brad.v

Reputation: 327

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;

Upvotes: 0

Related Questions