Concantenation of messages - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Concantenation of messages

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-04-30 : 19:08:44

Hi,

I have a table that has 4 fields and has free format text messages. The Identification field contains the client id
MNo field has Message Number specific client id. A single messge is split down into multiple rows and Seq field gives the relation which text message belongs to which MNo

Message table:
Identification MNo Seq Text
CLIENT-01980 001 001 This is the header record for client ABS.
CLIENT-01426 001 001 This is the Header Record for DAN client #1426
CLIENT-01327 001 001 This is the Header record for Glaxo Client
CLIENT-01327 002 001 Jen: pursuant to my conversation via phone with you and Mel on 9/20
CLIENT-01327 002 002 regarding the Pete relocation, there is concern as to whether this
CLIENT-01327 002 003 sale is good or not. It appears that the prospective buy
CLIENT-01327 002 004 er will be in tommorrow if he does not produce a firm fina
CLIENT-01327 002 005 ncing commitment by that date.
CLIENT-01327 003 001 Message for client A
CLIENT-09970 001 001 This is the header record for client Nestle

I need to create a query where i need to join all the text messages that belong to the same MNo and Identification into one field. The output should be like the following:

Output Message table:
Identification MNo Seq Text
CLIENT-01980 001 001 This is the header record for client ABS.
CLIENT-01426 001 001 This is the Header Record for DAN client #1426
CLIENT-01327 001 001 This is the Header record for Glaxo Client
CLIENT-01327 002 001 Jen: pursuant to my conversation via phone with you and Mel on 9/20 regarding the Pete relocation, there is concern as to whether this sale is good or
or not. It appears that the prospective buyer will be in tommorrow if he does not produce a firm financing commitment by that date.
CLIENT-01327 003 001 Message for client A
CLIENT-09970 001 001 This is the header record for client Nestle

In the above output table for Identification = CLIENT-01327 and Mno = 002 the entire text message has been concantenated into one field.

Is there any way that i can write in a single query to obtain the output file.. Please help.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-30 : 19:21:52

Write up a function that takes the identification and MNO values and returns a concatenated list of values.


Create function dbo.fn_getText(@clientid varchar(100), @mno varchar(10))
returns varchar(1000)
as
begin
	
declare @res varchar(1000)
select @res = isnull(@res, '') + ',' + text from YourTable where identification = @clientid and mno = @mno
set @res = right(@res, len(@res)-1)

return @res
end

Then from your code call, the function as:

 select * , dbo.fn_getText(identification, mno) from yourTable

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-04-30 : 20:13:32

Thanks Dinakar...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources