How to concatenate fields from mutiple records

table1
id message
1 abc
table2
id message
1 cde
1 fgh
1 ijk
Desired result
id message
1 abcdefghijkYou need to use coalesce function
Declare @.s nvarchar(200)
select @.s=coalesce(@.s+m,m) from
(select message from table1) as a
select @.s=coalesce(@.s+m,m) from
(select message from table2) as a
select @.s
Madhivanan|||culam
Write an UDF ,an idea is here.
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"culam" <culam@.discussions.microsoft.com> wrote in message
news:51890D92-ACA7-4FE5-A70A-08F0F48795B7@.microsoft.com...
> table1
> id message
> 1 abc
> table2
> id message
> 1 cde
> 1 fgh
> 1 ijk
> Desired result
> id message
> 1 abcdefghijk|||You cannot arrive at a logical solution with that sample data. Either you
must provide an indicator for the order of concatenation or you will have to
use an arbitrary order based on the physical materialization of the rows.
A good approach is to extract the data to the client tier and do the
concatenation and formatting leveraging the client's string manipulation
capabilities. Generic solutions in SQL are hacks, some of which you can find
at:
http://groups.google.ca/groups?selm...FTNGP09.phx.gbl
Anith