Concatenate string in SQL

1

Hi I need help in Concatenate the string in the following manner any help much appreciated.

exec sp_executesql N'SELECT [Key]
,[Parent]
,[ShortName]
,[monthDate]
,[TotalInterviews]
,[InterviewsClosed]
,[ClosingRate]
,[ClosingRateTarget]
,[ProposalRate]
,[TotalPropositions]
,[TotalFiles]
,[ProposalRateTarget]
,[TotalFilesTarget]
FROM [Reporting].[Dash].[GRI004C]
WHERE [key] IN (@p1)
AND CONVERT(DATETIME, monthDate) between @p2 AND @p3',N'@p1 nvarchar(169),@p2 datetime,@p3 datetime',@p1=N'R-12104627,R-1251252,R-1251393,R-1252539,R-1254386,R-1254821,R-1261252,R-1265774,R-1265875,R-1268517,R-1271252,R-127320,R-1273538,R-1274037,R-1281548,R-1281904,R-1291979',@p2='2015-01-01 00:00:00',@p3='2016-01-01 00:00:00'

2 
DECLARE @str VARCHAR(1000)

SELECT @str = ISNULL(@str + ',', '') + column
FROM    table
3

@khtan, this is not supported, even though is usually works. It can fail, however. 'FOR XML PATH('')' is the better way.

e.g.

declare @str varchar(8000)
with a as (
select a from (values
    ('1'),('2')
    )v(a)
)

select @str = stuff(
    (
    select ', ' + a
    from a
    for xml path('')
    )
    ,1,2,'')

select @str
4

Oh didn't realized that. How does it fails ?

5

see

here
and
here

1 Like
6

thanks :slightly_smiling: