How to put result of one column inside a string variable?

Question

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 :

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?

You may refer to this: [SQL Server convert select a column and convert it to a string](http://stackoverflow.com/questions/16193152/sql-server-convert-select-a-column-and-convert-it-to-a-string) — Pham X. Bach, Mar 06 '17 at 09:41
Are you searching for this? http://stackoverflow.com/questions/2105729/how-can-i-pull-a-list-of-ids-from-a-sql-table-as-a-comma-separated-values-strin — Lali, Mar 06 '17 at 09:58

score 3 · Accepted Answer · answered Mar 06 '17 at 09:56

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

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

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

Why have you used the first option. Not sure it requires the replace part. It can be done as shared by @BeanFrog. — DK5, Mar 06 '17 at 10:31
@DK5 The >>> and replace are to flaf the first comma could have used stuff if you rather. The first technique has many more possibilities than the XML Path, For example tokenized strings, dynamic mapping, etc. — John Cappelletti, Mar 06 '17 at 12:17

score 1 · Answer 2 · answered Mar 06 '17 at 09:59

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;

score 0 · Answer 3 · answered Mar 06 '17 at 09:37

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

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.

How to put result of one column inside a string variable?

3 Answers3