CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
user3639877
user3639877

Reputation:

Concatenate all Rows into single string

i need to concatenate all rows to display in a single string i wrote the same query for other table it works both queries gives me a last row value only

   declare @varaibelName nvarchar(max)
    set @cpx=''
    Select  @varaibelName=COALESCE(@varaibelName,'')+  FeildName From TableName
    select @varaibelName

or

 declare @varaibelName nvarchar(max)
    set @varaibelName=''
    Select  @varaibelName=@varaibelName+ FeildName From TableName  
    select @varaibelName

Upvotes: 2

Views: 101

Answers (1)

Kahn
Kahn

Reputation: 1660

That would be because your second to last value is NULL, so it'll effectively set the parameter to NULL, and then fetch the last remaining row.

See fiddle here for example.

To fix that, you can prepare for the null with for example ISNULL, like so (replace the SQL in the fiddle with this, and see what happens):

declare @variableName nvarchar(max)
Select  @variableName=COALESCE(@variableName,'')+  ISNULL(FieldName,'') From TableName
select @variableName

Better yet, you probably want to separate the columns from each other, so you can do this to add a comma inbetween them, then parse the extra comma off from the end of the string:

declare @variableName nvarchar(max)
Select  @variableName=COALESCE(@variableName,'')+  ISNULL(FieldName+', ','') From TableName
select @variableName = SUBSTRING(@variableName, 1, LEN(@variableName)-1)
select @variableName

EDIT:

HoneyBadger is absolutely right, in his comment below. Since I'm having a brainfart day regarding COALESCE, might as well go with this to avoid pointlessly using both:

declare @variableName nvarchar(max) set @variableName = ''
Select  @variableName= @variableName + ISNULL(FieldName+', ','') From TableName
select @variableName = SUBSTRING(@variableName, 1, LEN(@variableName)-1)
select @variableName

Upvotes: 1

Related Questions