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]

回答1:

Test this:

 DECLARE @result NVARCHAR(MAX)

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

回答2:

DECLARE @result varchar(1000)

SET @result = ''

SELECT @result = @result + StudentId + ',' FROM Student WHERE condition = xyz

select substring(@result, 0, len(@result) - 1) --trim extra "," at end

回答3:

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

回答4:

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

回答5:

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

回答6:

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;

回答7:

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

回答8:

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!