CODEWITHSUNDEEP
sqlsql-servert-sql
Rima Shahbazyan
Rima Shahbazyan

Reputation: 13

Convert column values into string

I'm trying to write e proc which will convert column values into a string separated with a character: ',' for example. it stucks on this line exec @maxcount = sp_executesql @temp and returns the value of @maxxount any suggestions how could I set a value of dynamic query execution so that it continued processing all the function an executed the correct answer.

ALTER procedure [dbo].[usp_stringConvert](
 @table_name varchar(101),
 @column_name varchar(100),
 @separator varchar(20)
 )
as 

declare @maxcount int, @temp nvarchar(1000)
declare @count int
declare @result varchar(1000)
set @result =''
set @count =1
set @temp= Concat('select count(', @column_Name ,') from ', @table_name)
exec @maxcount =  sp_executesql @temp
while (@count<@maxcount)
begin
    if @count!=1
        set @result+=@separator
    set @temp=Concat('select ' , @column_name ,' from ' , @table_name , 'where @count = row_number() over(order by (select (100)))')
    exec @temp = sp_executesql @temp
    set @result =CONCAT(@result, @temp)
    set @count+=1;
end
select @result;

Upvotes: 1

Views: 1030

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Using regular SQL in SQL Server, you can concatenate strings using:

select stuff( (select concat(separator, column_name)
               from table_name
               for xml path ('', type)
              ).value('.', nvarchar(max)
                     ), 1, len(separator), ''
             )

You should be able to turn this into dynamic SQL using:

declare @sql nvarchar(max);
declare @result nvarchar(max);

set @sql = N'
select @result = stuff( (select concat(@separator + [column_name]
                         from [table_name]
                         for xml path ('''', type)
                        ).value(''.'', nvarchar(max)
                               ), 1, len(@separator), ''
                      )
';

set @sql = replace(@sql, '[table_name]', quotename(@table_name));
set @sql = replace(@sql, '[column_name]', quotename(@column_name));

exec sp_executesql @sql,
                   N'@result nvarchar(max) output',
                   @result=@result output;

select @result;

Upvotes: 1

Related Questions