String Concatenation & Order by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

String Concatenation & Order by

Author

Topic

livezone
Starting Member

10 Posts

Posted - 2011-07-20 : 15:23:23

When I am using order by caluse with string concatenation it only gives me the last row.

select @x= Coalesce(@x + '|' , '') + data from table order by col1

Without order by all the data shown but it is not ordered.

Below is the link from Microsoft, Does this behaviour still exists in SQL 2005?
[url]http://support.microsoft.com/kb/287515/en-us[/url]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-07-20 : 16:01:33

I tried on sql 2005 and there were no issues.

declare @a as varchar(4000)
select @a = Coalesce(@a + '|' , '') + col1
from mytable
order by col2 desc
select @a

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

livezone
Starting Member

10 Posts

Posted - 2011-07-20 : 16:38:24

It does works but not all the time. I got last row when using order and all rows when not using order by?

Try the following code

DECLARE @Mon varchar(20)
DECLARE @Tue varchar(20)
DECLARE @Wed varchar(20)
DECLARE @Thu varchar(20)
DECLARE @Fri varchar(20)
DECLARE @Sat varchar(20)
DECLARE @Sun varchar(20)

DECLARE @StoreHours varchar(500)

Declare @counter_table table (
Idx smallint,
[Name] varchar(10) ,
Hours varchar(30) ) 

Set @Mon = '9:30am-7:00pm'
Set @Tue = '9:30am-7:00pm'
Set @Wed = '10:00am-6:00pm'
Set @Thu = '9:30am-7:00pm'
Set @Fri = '9:30am-7:00pm'


Insert into @counter_table 
Select 2 , 'Mon' , @Mon
Union all
Select 3 , 'Tue' , @Tue
Union all
Select 4 , 'Wed' , @Wed
Union all
Select 5 , 'Thu' , @Thu
Union all
Select 6 , 'Fri' , @Fri


;with Data1 as(  
select hours,    str(idx, 1)  + [name] as idxname,   
 row_number() over(order by hours, idx) - row_number() over(order by idx) as grp  
from @counter_table) ,
Data2 as (
select ltrim(min(idxname)) as mn, 	
	Case When min(idxname) = max(idxname) 
	Then Stuff(min(idxname),1,1,'') 
	else stuff(min(idxname),1,1,'') + '-' + stuff(max(idxname),1,1,'') end + ':' + hours as StoreHours
from Data1
group by hours, grp )
Select *
--@StoreHours = Coalesce(@StoreHours + '<br>' , '') + StoreHours
from Data2 
order by mn;

select @StoreHours

If I run this code it gives me this output
2Mon	Mon-Tue:9:30am-7:00pm
4Wed	Wed:10:00am-6:00pm
5Thu	Thu-Fri:9:30am-7:00pm


If uncomment the below line 
--@StoreHours = Coalesce(@StoreHours + '<br>' , '') + StoreHours
@StoreHours will only contain last row.

Thanks
Shafiq

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-20 : 17:15:50

strange problem. Once I corrected your coalesce:
@StoreHours = Coalesce(@StoreHours + '<br>' + StoreHours, StoreHours)

I noticed a couple things:
If I comment out the ORDER BY it works.
If I select into a #temp table and run your final statement from the #temp table then it also works with and without the order by.

Something about calling from the serialized CTEs is breaking it...still playing

EDIT:
I see your coalesce will variation also works - sorry about that.

EDIT2:
I finally also saw that you originally noticed the same thing with the order by. Man, I'm slow today :(

Be One with the Optimizer
TG

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-07-21 : 12:57:51

If you must use the cte, just put all results into a table before you run


DECLARE @Mon varchar(20)
DECLARE @Tue varchar(20)
DECLARE @Wed varchar(20)
DECLARE @Thu varchar(20)
DECLARE @Fri varchar(20)
DECLARE @Sat varchar(20)
DECLARE @Sun varchar(20)

DECLARE @StoreHours varchar(500)

Declare @counter_table table (
Idx smallint,
[Name] varchar(10) ,
Hours varchar(30) ) 

Set @Mon = '9:30am-7:00pm'
Set @Tue = '9:30am-7:00pm'
Set @Wed = '10:00am-6:00pm'
Set @Thu = '9:30am-7:00pm'
Set @Fri = '9:30am-7:00pm'


Insert into @counter_table 
Select 2 , 'Mon' , @Mon
Union all
Select 3 , 'Tue' , @Tue
Union all
Select 4 , 'Wed' , @Wed
Union all
Select 5 , 'Thu' , @Thu
Union all
Select 6 , 'Fri' , @Fri


;with Data1 as(  
select hours,    str(idx, 1)  + [name] as idxname,   
 row_number() over(order by hours, idx) - row_number() over(order by idx) as grp  
from @counter_table) ,
Data2 as (
select ltrim(min(idxname)) as mn, 	
	Case When min(idxname) = max(idxname) 
	Then Stuff(min(idxname),1,1,'') 
	else stuff(min(idxname),1,1,'') + '-' + stuff(max(idxname),1,1,'') end + ':' + hours as StoreHours
from Data1
group by hours, grp )
Select *
into #tmp
from Data2 
order by mn;

select @StoreHours = coalesce(@StoreHours + '<br>' , '') + StoreHours
from #tmp
drop table #tmp
select @StoreHours

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

livezone
Starting Member

10 Posts

Posted - 2011-07-21 : 13:23:00

But why this problem exists with CTE?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-07-21 : 15:26:21

I do not know why there is a issue with a CTE, but I would personally not use a CTE, this method should significantly outperform the method you illustrated on large data sets, and a clustered index has been proven pretty reliable used in this fashion.


DECLARE @Mon varchar(20)
DECLARE @Tue varchar(20)
DECLARE @Wed varchar(20)
DECLARE @Thu varchar(20)
DECLARE @Fri varchar(20)
DECLARE @Sat varchar(20)
DECLARE @Sun varchar(20)

DECLARE @StoreHours varchar(500)
DECLARE @count int

Declare @counter_table table (
Idx smallint,
[Name] varchar(10) ,
Hours varchar(30) ) 

Declare @counter_tablewrk table (
Idx smallint primary key,
[Name] varchar(10) ,
Hours varchar(30) ,
usethis bit,
running varchar(2000),
mygroup int,
mygroupcount int) 

Set @Mon = '9:30am-7:00pm'
Set @Tue = '9:30am-7:00pm'
Set @Wed = '10:00am-6:00pm'
Set @Thu = '9:30am-7:00pm'
Set @Fri = '9:30am-7:00pm'


Insert into @counter_table 
Select 2 , 'Mon' , @Mon
Union all
Select 3 , 'Tue' , @Tue
Union all
Select 4 , 'Wed' , @Wed
Union all
Select 5 , 'Thu' , @Thu
Union all
Select 6 , 'Fri' , @Fri

insert into @counter_tablewrk(Idx,
[Name],
Hours)
select Idx,
[Name],
Hours
from 
@counter_table
order by idx

declare @holder varchar(2000),@anchor int,@lasttime varchar(20),@minday varchar(20),@mygroup int,@mygroupcount int
set @mygroup =0
set @mygroupcount =0 

update a
set 
@holder = running = case 
		  when @lasttime is null then [name] + ':' + hours
		  when @lasttime = hours then @minday + '-' + [name] + ':' + hours 
		  else  [name] + ':' + hours --this is a use
		  end 	
,@mygroup = mygroup =  case 
		  when @lasttime = hours then @mygroup 
		  when @lasttime is null then @mygroup 
		  else @mygroup + 1
		  end 	
,@mygroupcount = mygroupcount = case 
		  when @lasttime = hours then @mygroupcount + 1
		  when @lasttime is null then @mygroupcount + 1
		  else 1
		  end 	
,@minday = case when @lasttime = hours then @minday else [name] end
,@lasttime = hours
,@anchor = idx
from
@counter_tablewrk a

select running
from
(
select row_number() over (partition by mygroup order by mygroupcount desc) as rowid,* from @counter_tablewrk a
) aa
where aa.rowid = 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources