How to do this

Hi All,
I have a table Called MailList with 2 columns namely MailId and Message.
There can be many messages related to one email id in the table. I need to
consolidate all the messages into a single message and send the mail to that
id.
(It may or may not exceed the limit of 8000 chars).
For Example,
MailId Message
a@.x.com XXXXXXXXXXXXXXXXXXXXXX
a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
c@.z.com BB
a@.x.com CCC
I need to concatenate the messages of the first, second and fifth record
into one single message and send the mail to a@.x.com. Similarly for the othe
r
two.
How can I do this. Please help me at the earlist.
Sevugan.CYou have to do this in a loop. Wheter you do it in a temporary table or
within a cursor belongs to you coding style.
HTH, Jens Suessmeyer.|||use northwind
-- your problem looks like this --
select supplierid, productname
from products
-- your problem looks like this --
-- solutions--
create proc concatenate
(
@.supplierid Varchar(10)
)
as
select IDENTITY(int, 1,1) AS ID_Num,
Productname into #tempx from products where
supplierid=@.supplierid
declare @.ctr int
declare @.concatenated varchar(5000)
declare @.maxrecord int
select @.maxrecord=max(id_num) from #tempx
select @.concatenated=''
select @.ctr=0
while @.ctr <> @.maxrecord
begin
Select @.ctr=@.ctr+1
Select @.concatenated =@.concatenated+Productname
from #tempx where id_num=@.ctr
end
select @.concatenated
--test solution --
exec concatenate 7
try to improve from there...
thanks,
joeydj
"Sevugan" wrote:

> Hi All,
> I have a table Called MailList with 2 columns namely MailId and Message.
> There can be many messages related to one email id in the table. I need to
> consolidate all the messages into a single message and send the mail to th
at
> id.
> (It may or may not exceed the limit of 8000 chars).
> For Example,
> MailId Message
> a@.x.com XXXXXXXXXXXXXXXXXXXXXX
> a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
> b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> c@.z.com BB
> a@.x.com CCC
> I need to concatenate the messages of the first, second and fifth record
> into one single message and send the mail to a@.x.com. Similarly for the ot
her
> two.
> How can I do this. Please help me at the earlist.
> --
> Sevugan.C|||Which is the PK? You want message to be XXX...YYYY...CCC or
CCCXXX...YYY....' If youcan post your DDL fo rthe table, it will be
helpful for us.
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:DAF1AC53-008E-493A-93B0-A82F557E49FD@.microsoft.com...
> Hi All,
> I have a table Called MailList with 2 columns namely MailId and Message.
> There can be many messages related to one email id in the table. I need to
> consolidate all the messages into a single message and send the mail to
> that
> id.
> (It may or may not exceed the limit of 8000 chars).
> For Example,
> MailId Message
> a@.x.com XXXXXXXXXXXXXXXXXXXXXX
> a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
> b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> c@.z.com BB
> a@.x.com CCC
> I need to concatenate the messages of the first, second and fifth record
> into one single message and send the mail to a@.x.com. Similarly for the
> other
> two.
> How can I do this. Please help me at the earlist.
> --
> Sevugan.C|||It is a temporary table. The Message is the primary key.
I want the message to be xxx...yyy...ccc...
--
Sevugan.C
"Perayu" wrote:

> Which is the PK? You want message to be XXX...YYYY...CCC or
> CCCXXX...YYY....' If youcan post your DDL fo rthe table, it will be
> helpful for us.
> Perayu
> "Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
> news:DAF1AC53-008E-493A-93B0-A82F557E49FD@.microsoft.com...
>
>|||Hi
My problem here is I need to store more than 8000 characters which is not
permitted in VARCHAR variable.
If the no.of records in the table are more than 8000 chars how can I store
that?
--
Sevugan.C
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> use northwind
> -- your problem looks like this --
> select supplierid, productname
> from products
> -- your problem looks like this --
> -- solutions--
> create proc concatenate
> (
> @.supplierid Varchar(10)
> )
> as
> select IDENTITY(int, 1,1) AS ID_Num,
> Productname into #tempx from products where
> supplierid=@.supplierid
> declare @.ctr int
> declare @.concatenated varchar(5000)
> declare @.maxrecord int
> select @.maxrecord=max(id_num) from #tempx
> select @.concatenated=''
> select @.ctr=0
>
> while @.ctr <> @.maxrecord
> begin
> Select @.ctr=@.ctr+1
> Select @.concatenated =@.concatenated+Productname
> from #tempx where id_num=@.ctr
> end
> select @.concatenated
> --test solution --
> exec concatenate 7
>
> try to improve from there...
> --
> thanks,
> --
> joeydj
>
> "Sevugan" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

>From your vague narrative, you need a column that gives you the message
order.
Finally, you need to learn RDBMS and *basic* tiered architecutre
concepts. This is a display issue and shjould be done in the front
end, never in the database.|||Why can't you use Text column?
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:7DC75F76-DC08-488A-81DB-90B676261903@.microsoft.com...
> Hi
> My problem here is I need to store more than 8000 characters which is not
> permitted in VARCHAR variable.
> If the no.of records in the table are more than 8000 chars how can I store
> that?
> --
> Sevugan.C
>
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||If message is the PK, so you have no control which one will be the first
one.
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:B76CCD95-E2F9-4315-BA77-D2B9C614CE76@.microsoft.com...
> It is a temporary table. The Message is the primary key.
> I want the message to be xxx...yyy...ccc...
> --
> Sevugan.C
>
> "Perayu" wrote:
>|||How would I pick the value from that text column to make use of that in the
xp_sendmail procedure call.
Exec Master..xp_sendmail @.message = <how to bring the value of the text
column here>
Sevugan.C
"Perayu" wrote:

> Why can't you use Text column?
> Perayu
> "Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
> news:7DC75F76-DC08-488A-81DB-90B676261903@.microsoft.com...
>
>