vipin_nagarro
asked on
SQL awkward behaviour (urgent)
Hi All
Below I have attached the code where im getting some problem.
This will first create a temp table named #t1 with one column c1 with data as follows:
3A
1B
2C
I need to get comma seperated string with order of numeral part (excluding last char), so I cast it like cast(SUBSTRING(c1, 0, len(c1)) as int) in order by clause.
Please run the attached query.
Now the result Im getting is a bit awkward.
@var returns '3A,' but I was expecting '1B,2C,3A'.
Could some body tell me why such a behaviour.
Also let me know the alternate approach for this.
Please help.
Thanks
Vipin Goel
Below I have attached the code where im getting some problem.
This will first create a temp table named #t1 with one column c1 with data as follows:
3A
1B
2C
I need to get comma seperated string with order of numeral part (excluding last char), so I cast it like cast(SUBSTRING(c1, 0, len(c1)) as int) in order by clause.
Please run the attached query.
Now the result Im getting is a bit awkward.
@var returns '3A,' but I was expecting '1B,2C,3A'.
Could some body tell me why such a behaviour.
Also let me know the alternate approach for this.
Please help.
Thanks
Vipin Goel
create table #t1 (c1 varchar(5))
insert into #t1 values ('3A')
insert into #t1 values ('1B')
insert into #t1 values ('2C')
declare @var varchar(100)
set @var = ''
select @var = @var + c1 + ','
from #t1
order by cast(SUBSTRING(c1, 0, len(c1)) as int)
select * from #t1
select cast(c1 as varchar)
from #t1
order by cast(SUBSTRING(c1, 0, len(c1)) as int)
select @var
drop table #t1
ASKER
@angelll - this is a completely new approach, the code i have given is already implemented. So it will be difficult to turn to this.
Also I am more interested in the reason for such a behaviour.
Also I am more interested in the reason for such a behaviour.
Hi,
try the following
create function [dbo].[fn_Ttest]
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @Result VARCHAR(200)
SELECT @Result = stuff(
(
select
', ' + c1
from
#t1
FOR XML PATH('')), 1, 2, '')
RETURN @Result
END
try the following
create function [dbo].[fn_Ttest]
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @Result VARCHAR(200)
SELECT @Result = stuff(
(
select
', ' + c1
from
#t1
FOR XML PATH('')), 1, 2, '')
RETURN @Result
END
I tested this:
select @var = coalesce(@var + ',', '') + c1
from ( select c1 , row_number() over (order by cast(SUBSTRING(c1, 0, len(c1)) as int)) rn
from #t1
) sq
order by rn
ASKER
Im working on DB compatibility of 80, so i cant use row_number().
ok, this worked also:
select @var = coalesce(@var + ',', '') + c1
from ( select c1
from #t1
order by cast(SUBSTRING(c1, 0, len(c1)) as int)
) sq
order by rn
ASKER
@angell - Not working
(The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.)
(The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It Worked Thanks
http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html