August 1, 2008 at 3:20 pm
Hello,
When I use COALESCE to concatenate a string over many periods, it gave me the string backwards.
Here is my sample:
-- drop table #test,#test_desc
set nocount on
create table #test (ID int, letter char(1), period int )
insert into #test (ID, letter,period) select '01','A', 120
insert into #test (ID, letter,period) select '01','B', 121
insert into #test (ID, letter,period) select '01','C', 122
--using the table #test as is, gives me the results backwards --"CBA"
--It does the same when I select * from #test order by period ascending
DECLARE @string varchar(360)
set @string = ' '
SELECT @string = COALESCE(letter + ' ', '') + @string from #test
SELECT @string --This gives me "CBA"
---I have to create a descending order by period to get my desired results
select * into #test_desc from #test order by period desc
set @string = ' '
SELECT @string = COALESCE(letter + ' ', '') + @string from #test_desc
SELECT @string --This gives me "ABC" which is my desired output
Does COALESCE work backwards in this case, or am I missing the point.:w00t:
Thanks in advance,
Michael
August 1, 2008 at 3:39 pm
The problem isn't the COALESCE, you are adding each subsequent letter to the beginning of the string:
SELECT @string = COALESCE(letter + ' ', '') + @string from #test
To add to the end of the string, it should be:
SELECT @string = @string + COALESCE(letter + ' ', '') from #test
August 1, 2008 at 3:42 pm
Micheal
I guess COALESCE works backward , as i test too .
Good finding , i usaully overlook this point before while using COALESCE function.
Cheers
August 1, 2008 at 3:42 pm
However, without a Clustered Index or an "Order BY", these rows could still come back in any order.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 1, 2008 at 3:45 pm
Thank you very much Steven. I've been staring at this for the past few hours and to get a very quick answer on a Friday afternoon just made my day. Problem solve! thanks again:)
August 1, 2008 at 3:49 pm
Change the related part as below.
DECLARE @string varchar(360)
set @string = ' '
--SELECT @string = COALESCE(letter + ' ', '') + @string from #test
SELECT @string = @string + COALESCE(letter + ' ', '') from #test
SELECT @string --This gives you " A B C "
August 1, 2008 at 3:52 pm
DECLARE @string varchar(360)
set @string = ''
SELECT @string = @string + letter + ' ' from #test
SELECT @string
August 1, 2008 at 3:56 pm
Deee -Daah! (8/1/2008)
Hello,When I use COALESCE to concatenate a string over many periods, it gave me the string backwards.
--using the table #test as is, gives me the results backwards --"CBA"
--It does the same when I select * from #test order by period ascending
DECLARE @string varchar(360)
set @string = ' '
SELECT @string = COALESCE(letter + ' ', '') + @string from #test
SELECT @string --This gives me "CBA"
---I have to create a descending order by period to get my desired results
select * into #test_desc from #test order by period desc
set @string = ' '
SELECT @string = COALESCE(letter + ' ', '') + @string from #test_desc
SELECT @string --This gives me "ABC" which is my desired output
Does COALESCE work backwards in this case, or am I missing the point.:w00t:
Thanks in advance,
Michael
Michael, this is not a problem with COALESCE. What you are running into is the problem with building a string this way, and that is that the results can be return in whatever order SQL decides. When you put the ORDER BY on it, you are ordering by the same value - which allows SQL Server to return the rows in any order as long is it is ordered by orderid.
There are better methods for doing this - if you search this site for the article by Jeff Moden using the Tally table, I think that has several examples that will be very useful for you.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply