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?
