如何将一列的结果放在字符串变量中?

I try to select number of rows and then put them into string variable such as 1,2,3,4,5, but get this error :

我尝试选择行数，然后将它们放入字符串变量中，例如1、2、3、4、5，但是得到这个错误:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

子查询返回超过一个值。当子查询后跟=、!=、<、<=、>、>=或子查询用作表达式时，不允许这样做。

I use MSSQL SERVER

我使用该服务器

    DECLARE @CodeNameString VARCHAR(MAX)

SELECT @CodeNameString = STUFF(
           (
               SELECT dbo.CharterReference.TicketNo+','
               FROM   dbo.CharterReference

           ),
           1,
           1,
           ''
       )

SELECT @CodeNameString

How can i fix this?

我该怎么解决这个问题呢?

3 个解决方案

#1

If you want the values in @CharterReference, you can use the following

如果您想要@CharterReference中的值，可以使用以下内容

Declare @CharterReference table (TicketNo int)
Insert Into @CharterReference values
(1),(2),(3),(4),(5),(6),(7),(8)

Declare @CodeNameString  varchar(Max) = '>>>'
Select @CodeNameString =replace(concat(@CodeNameString ,',',TicketNo),'>>>,','')
 From  @CharterReference
 Where TicketNo between 1 and 5
 Order By TicketNo

Select @CodeNameString

Returns

1,2,3,4,5

Or you can use a little XML

也可以使用一点XML

Select @CodeNameString=Stuff((Select ',' +cast(TicketNo as varchar(25)) 
                               From  @CharterReference 
                               Where TicketNo between 1 and 5 
                               For XML Path ('') 
                              ),1,1,'')

#2

The error message is complaining because you have multiple rows returned, and that is not allowed in the way you have done this.

错误消息是抱怨，因为您返回了多个行，这是不允许的。

Rearranging to a normal looking select statement, and then lopping off the last comma afterwards would work around this:

重新排列到一个正常的选择语句，然后将最后一个逗号剪掉，然后在这里工作:

DECLARE @CodeNameString VARCHAR(MAX);
set @CodeNameString = '';

SELECT @CodeNameString = TicketNo + ','
FROM   dbo.CharterReference;

SELECT left(@CodeNameString, len(@CodeNameString)-1) as CodeNameString;

#3

The SQL-based solution requires you to use recursive SQL for this. The syntax is typically DBMS-specific, and guessing by the syntax you're using in the example, O believe your engine calls this feature "recursive CTE".

基于SQL的解决方案要求您为此使用递归SQL。语法通常是特定于dbms的，根据示例中使用的语法，O相信您的引擎将此特性称为“递归CTE”。

The alternative is to cursor over the result set with the individual row and construct the string append in your client program.

另一种方法是用单个行在结果集上游标，并在客户端程序中构造附加的字符串。

Yet another alternative is to use the PL/SQL dialect of your system. You can then write an SQL procedure where you do the cursoring over the result set and the string appending. You can expose this SQL procedure as a callable module to your client programs.

另一种选择是使用系统的PL/SQL方言。然后，您可以编写一个SQL过程，在其中对结果集和字符串追加进行排序。您可以将这个SQL过程作为可调用模块公开给您的客户端程序。

#1