我在SQL Server中得到的结果是:
SELECT StudentId FROM Student WHERE condition = xyz
我得到的输出结果如下:
StudentId 1236
7656
8990 ........
存储过程的输出参数是字符串@studentId
,我想返回语句为
1236, 7656, 8990.
如何将输出转换成单个字符串?
我正在返回单个列[即学生ID]
我在SQL Server中得到的结果是:
SELECT StudentId FROM Student WHERE condition = xyz
我得到的输出结果如下:
StudentId 1236
7656
8990 ........
存储过程的输出参数是字符串@studentId
,我想返回语句为
1236, 7656, 8990.
如何将输出转换成单个字符串?
我正在返回单个列[即学生ID]
测试这个:
DECLARE @result NVARCHAR(MAX)
SELECT @result = STUFF(
( SELECT ',' + CONVERT(NVARCHAR(20), StudentId)
FROM Student
WHERE condition = abc
FOR xml path('')
)
, 1
, 1
, '')
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
substring(@result, 1, len(@result) )
可以去除尾随的逗号,而上面的版本也会把逗号前面的字符一起去掉。 :) - asyncsubstring(@result, 1, len(@result) - 1)
。参考链接:http://msdn.microsoft.com/zh-cn/library/ms187748.aspx - Robino使用 COALESCE
函数:
DECLARE @StudentID VARCHAR(1000)
SELECT @StudentID = COALESCE(@StudentID + ',', '') + StudentID
FROM Student
WHERE StudentID IS NOT NULL and Condition='XYZ'
select @StudentID
两个答案都是正确的,但不要忘记初始化变量的值,默认值为NULL,并使用T-SQL:
NULL + "Any text" => NULL
这是一个非常普遍的错误,不要忘记它!
此外,使用ISNULL函数是一个好主意:
SELECT @result = @result + ISNULL(StudentId + ',', '') FROM Student
使用CONCAT
函数可避免转换错误:
DECLARE @StudentID VARCHAR(1000)
SELECT @StudentID = CONCAT(COALESCE(@StudentID + ',', ''), StudentID)
FROM Student
WHERE StudentID IS NOT NULL and Condition='XYZ'
select @StudentID
SELECT StudentId FROM Student WHERE condition = xyz
原始结果集...StudentId
1236
7656
8990
使用concat函数创建新查询...
SELECT group_concat(concat_ws(',', StudentId) separator '; ')
FROM Student
WHERE condition = xyz
连接字符串结果集...
StudentId
1236; 7656; 8990
这是正确的代码,几乎像brad.v的,但有一个重要的改变:
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;
SELECT STRING_AGG(sub.StudentId, ',') FROM
(select * from dbo.Students where Name = 'Test3') as sub
如果想使用例如 ORDER BY:
SELECT STRING_AGG(sub.StudentId, ',') WITHIN GROUP(Order by StudentId) FROM
(select * from dbo.Students where Name = 'Test3') as sub
这个方法适用于表中的NULL值,并且不需要在末尾进行子字符串操作。如果表中存在NULL值,COALESCE函数不能很好地处理它们。
DECLARE @results VARCHAR(1000) = ''
SELECT @results = @results +
ISNULL(CASE WHEN LEN(@results) = 0 THEN '' ELSE ',' END + [StudentId], '')
FROM Student WHERE condition = xyz
select @results
或者一个单独的选择语句...
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;