将SQL Server结果集转换为字符串

I am getting the result in SQL Server as

我将在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

存储过程的输出参数是@studentId字符串，我希望返回语句为

1236, 7656, 8990.

How can I convert the output in the single string?

如何在单个字符串中转换输出?

I am returning single column [ie. StudentId]

我要退一栏。StudentId]

8 个解决方案

#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

Both answers are valid, but don't forget to initializate the value of the variable, by default is NULL and with T-SQL:

这两个答案都是有效的，但是不要忘记初始化变量的值，默认情况下是NULL，使用T-SQL:

NULL + "Any text" => NULL

It's a very common mistake, don't forget it!

这是一个很常见的错误，别忘了!

Also is good idea to use ISNULL function:

使用ISNULL函数也不错:

SELECT @result = @result + ISNULL(StudentId + ',', '') FROM Student

#4

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

#5

Use the CONCAT function to avoid conversion errors:

使用CONCAT函数避免转换错误:

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).

这个函数在表中使用NULL值，并且在结束时不需要子字符串操作。对于表中的空值(如果存在的话)，联合并不是很好地工作。

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.

答案从布拉德。v是错误的!它不会给你一个连接字符串。

Here's the correct code, almost like brad.v's but with one important change:

这是正确的代码，就像brad一样。但是有一个重要的变化:

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!

看出不同了吗?布莱德:)。请把你的答案写下来，我无法做任何修改或评论，因为我在这里的声誉是零。我想我可以在你修好你的后把我的拿走。谢谢!

#1