November 27, 2014 at 5:32 am
Create table test(Names varchar(100) primary key )
insert into test values('Hugeman')
insert into test values('Jack')
insert into test values('William')
insert into test values('Kevin')
insert into test values('Peter')
Query 1:
declare @sql varchar(100)
select @sql = coalesce(@sql+'+','')+Names from test order by names-- where object_id =object_id('temp')
print @sql
This will result as
Hugeman+Jack+Kevin+Peter+William
Query 2
declare @sql varchar(100)
select @sql = coalesce(Names+'+','') from test order by names-- where object_id =object_id('temp')
print @sql
This will results William+
As per the documentation of coalesce, will return the first not null value. So it has to result Hugeman+. But it returns the entire rows.How & Why ?
Why query2 haven't done the same ?
November 27, 2014 at 7:11 am
The query2 problem is not in Coalesce function.
The query2 is not concatenating the value of the variable @SQL with the value of Names column, so the result is the last value.
Hope this helps.
November 27, 2014 at 8:37 am
Coalesce returns the first non-null value from a series of values in the same row. These values need to be included as parameters delimited by commas. eg. COALESCE(value1, value2, value3, valueN).
To get the first non-null value from a column, you need to have an aggregate function. Aggregate functions take a set of rows and return a single row.
And finally, there's no such thing as a first value in a table. With 5 values and an index, you'll see a consistency in the order, but with larger volumes you might not get the same order due to parallelism. Order in a select can only be ensured by using ORDER BY.
November 27, 2014 at 8:43 am
that is a cool way to concatenate strings, much easier than FORXML and STUFF()
November 27, 2014 at 8:56 am
aaron.reese (11/27/2014)
that is a cool way to concatenate strings, much easier than FORXML and STUFF()
There are some problems with this method. For example, the order is not guaranteed and you can't have multiple concatenated strings as the following example.
Create table test(id int, Names varchar(100) primary key )
insert into test values(1,'Hugeman'),(1,'Jack'),(2,'William'),(2,'Kevin'),(2,'Peter')
SELECT id,
STUFF((SELECT '+' + x.Names
FROM Test x
WHERE t.id = x.id
ORDER BY x.Names
FOR XML PATH('')), 1,1, '')
FROM test t
GROUP BY id
drop table test
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply