Need to combine string data from multiple columns into one column

When quering a table with given criteria, For ex:

select notes, jobid, caller from contact where status in (6) and jobid = 173

I am getting this:

This job will be posted to Monster for 2 weeks. 173 906
Waiting for full budget approval 173 906
TUrns out we're uppin 173 906

What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon?

Please HELP!!!!!

Concatenating row values in Transact-SQL

http://www.projectdmx.com/tsql/rowconcatenate.aspx

AMB

|||

You can concantenate the results, but you need to ensure that you have converted all the different data types to varchar. E.g.

Code Snippet

SELECT notes + ' , ' + CAST(jobid as varchar(100)) + ' , ' + CAST(caller as varchar(100)) FROM contact WHERE status in (6) and jobid = 173

I assumed that jobid and caller are int fields.

HTH

Ray

|||

Here it is (if you use SQL Server 2005),

Code Snippet

select distinct

(

select

notes + ';' as [text()]

from

contact sub

where

sub.caller=main.caller

and sub.jobid=main.jobid

for xml path('')

) as notes,

jobid,

caller

from

contact main

where

status in (6)

and jobid = 173

|||

Thanks for the response sekaran. I should have mentioned it before, I am using sql server 2000 using tsql language. Also the notes columns is of text type which I will cast as nvarchar(3500). I am having problems running you code. What am i doing wrong? can u help?