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?

Need SQL Help

I have following query which is joining couple of tables. i have a field "Status" in MeetingAttendees table. I have to add one more check (probably one more case statement) that if MA.Status=4 then Count(A.AttendeeID) as NoofAttendees. So NoofRSVPs (doesn't matter what is the status in MeetingAttendees table) will return total RSVPs and NoofAttendee will return only # of Attendees. how can i add do that? please help...

SELECT

M.State AS MeetingState,

CASE
WHEN MA.AttendeeType = 1 THEN 'Participant'
WHEN MA.AttendeeType = 2 THEN 'Speaker/Faculty'
WHEN MA.AttendeeType = 3 THEN 'Client'
WHEN MA.AttendeeType = 4 THEN 'Staff'
END AS AttendeeType,

Count(A.AttendeeID) as NoofRSVPs

FROM
Programs P
INNER
JOIN eCDReservations M
ON P.SubCompanyCode = M.SubCompanyCode
AND P.ProgramCode = M.ProgramCode
left outer
JOIN MeetingAttendees MA
ON M.ReservationID = MA.MeetingID
left outer
JOIN Attendees A
ON MA.AttendeeID = A.AttendeeID
left outer
JOIN Regions R
ON MA.RegionCode = R.RegionCode
WHERE
P.SubCompanyCode = @.SubCompanyCode AND
P.ProgramCode = @.ProgramCode




GROUP BY

M.State,

MA.AttendeeType

ORDER BY

MA.AttendeeTypeCOUNT(CASE WHEN MA.Status=4 THEN 'present' END) as NoofAttendees

Need some helps on this task

A user photo table with the following fields:

username
path
caption
status

where the status field indicates where the photo is the main one or not. Each user only can have one main photo.

For the deletion operation, another, if there is one, photo's status needs to be changed to main if the removing photo is the main one. What is the best approache to carry out this task: SQL and function/trigger?

Similar situation occur when a user want to change a non-main photo to become a main one.

Any advice?

Thanks,

v.While a trigger could be made to do this, it would not be trivial - would have to deal with the mutating table problem for one thing.

I would prefer to hide the logic in a packaged procedure, and force the user (i.e. the application) to delete via the procedure rather than an update or delete statement.|||Hi, Tony,

Thanks for your response and suggestion.

My thought on the issue is that it is somehow like a DB table constraint and not a business logic. Therefore, it shoud be resolved in the DB layer.

I can implement your suggestion to pass the information what the application knows about whether the photo is a main one or not. I think this solution is a suitable one.

Thanks again.

v.|||Here is a solution I just come out.

For the deletion operation, if the deleting photo placement is larger than 1, not the first/main one, only execute the deletion statement.

Otherwise, after the deletion statement, run the following query:

UPDATE photo SET main = 'true' WHERE userid = 'xxx' AND path IN (SELECT path FROM photo WHERE userid = 'xxx' )

This solution basically is on the DB side with a little help from the application (logic).

My feeling of the above query is the subquery can be in a better form, but can't think out one at this moment.

v.

Need Help...-sqlmangr.exe

I have the sql 7.0 and sql 2000 server installed.
On restart the sqlmangr shows the status of the 7.0 server.
I want to see the status of the 2000 server.
How can i change this ?
Thanxopen up SQL Server Service Manager .. look at "Server" on the right of that box, there is a drop down menu arrow. Click it ;) Or you can just type over the sql server name.