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 Page break for Every Odd Page

Hi,
I have a table with group which populated on multiple pages. If the group
ended with odd page then I need an extra page break (1 blank page).
pls get me the details on that.
Thanks & Regards
Gopi RHi,
I expecting some Suggestion but no one answer to this question. pls give
the suggestion.
Thanks & Regards
Gopi R
"Gopala Krishnan" <Gopal@.photoninfotech.com> wrote in message
news:uhB83VCYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a table with group which populated on multiple pages. If the group
> ended with odd page then I need an extra page break (1 blank page).
> pls get me the details on that.
> Thanks & Regards
> Gopi R
>
>
>

Need help with UDF useage. Trying to get away without using cursor.

I have a Function (say X) that takes 2 parameters and returns back a table result of multiple records.

And I have a query (say Q) that return rows of 2 columns that I need to feed Function X.

They way I do it right now is I have a cursor that loops through the result of Query Q
and call Function X as I pass the 2 values the the function.

As Function X return with the result set, I load it into a temporary table.

At the end of the cursor processing, I query the temporary table to return the complete result set.

Is there a way do this without using a cursor?

Here is my Function X top part:

alter FUNCTION ReturnItem
(
@.tableName varchar(50),
@.ItemID int
)
returns @.returnTable table
(
ItemName varchar(50),
ItemValue varchar(50),
[Timestamp] datetime
)

JB..

You want to use the CROSS APPLY capability of SS2k5 to "apply" the rows of one table to a UDF. Here is an example:

CREATE TABLE QuerySource
(
c1 INT,
c2 INT
)

INSERT QuerySource VALUES (1,1)
INSERT QuerySource VALUES (2,3)
INSERT QuerySource VALUES (10,15)
INSERT QuerySource VALUES (16,13)

CREATE FUNCTION ReturnItem
(
@.p1 INT,
@.p2 int
)
returns @.returnTable table
(
AddResult int,
SubtractResult int,
TimesResult int,
DivideResult int
)
AS
BEGIN
INSERT @.returnTable SELECT @.p1+@.p2, @.p1-@.p2,@.p1*@.p2,@.p1/@.p2
RETURN
END

SELECT *
FROM QuerySource qs CROSS APPLY dbo.ReturnItem(qs.c1, qs.c2)

|||Hello. Thank you very much for you help. That is exactly what I wanted to do. I never knew such feature exist. Anyway, I was afraid that when the number of data being passed becomes really big, the cursor will slow things down hence, I have to find this solution.

Thank you again.

JB..