merge columns in one row

[cardi]

Hi,

I did that:

Select * from OppSalesTeam where role = 'Financing' and Opportunity = '123456';

The result is this:
ID role name Opportunity
2143 Financing bank 123456
4141 Financing owner 123456


But, I would that the result seem like that:
ID role name Opportunity
2143 Financing bank ; owner 123456

I just want 1 row that contains the names for this Opportunity, but I don't know how I can do that.

I tried this code...

CREATE FUNCTION dbo.ConcatSourceOfFinancing ( @Opportunity_ID varchar(32) )
RETURNS varchar(400) AS
BEGIN

DECLARE @value varchar(400)
SET @value = ''

SELECT @value = @value + Org.OrgName + ' ; '
FROM OppSalesTeam OST inner join org on (ost.org_id = org.org_id)
WHERE role = 'Financing' and OST.Opportunity_ID = @Opportunity_ID
ORDER BY Org.OrgName

if @value <> ''
set @value = left(@value, len(@value) - 2)

Return @value
END -- Function

It works well, but if I have over 1000 records, it fails. Maybe because call this function over 1000 times, the server don`t like that.

What can I do with this function to be faster and more robust???

Thank you

 

[gmmastros]

>> but if I have over 1000 records, it fails.

In what way does it fail. Presumably, you are getting an error message. What is the error message?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cardi]

The problem is.... I don't know because it works in a marketing application, so the error message is...

"A error as occurs, if problem persist contact administrator"

I can't try it in SQL server directly, because I need two much of parameter. This section is only a part of the request. but I will try to make it directly on server and I will give you the error.

Thanks

 

[cardi]

It works well directly on server. The data are transfered the server to Crystal reports and after to Excel... The problem can be there

 

[gmmastros]

When you run it directly on the server, how long does it take?


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Allright. Here's what I'm thinking... you could have performance issues. Run the following querys directly on the server (in query analyzer), and post the output here.

sp_helpindex 'OppSalesTeam'
sp_helpindex 'org'



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cardi]

for 3700 records it takes 7 seconds directly on server. That's good. But I think that the problem is if I have 3 Sources Of Financing, the transfer to crystal fails because "the rows will be truncated".

I still use my function above but i replaced the 400 for 8000 juste to be sure.

 

[cardi]

I think that I fin the problem... Before, when the data is passing to crystal, the lenght of field was 40. But now, with concatenation, I need a lenght of 1000 (don't take chance!!)

How can I change the len of the field in Crystal???

Thanks

 

[cardi]

I forget to tell you that the query is in a string and we to the execution of the string

I tried a cast for my function but i got a syntx error near of AS. This my call

"' +(cast( + '"(dbo.ConcatSourceOfFinancing (SLI_VIEW_FIND_LEADS_WKGRP.Opportunity_ID)) "' + AS varchar(1000))) + '"AS SourceOfFinancing'

 

[cardi]

I'm wrong since the beginning. The problem was not in my function, but in the transfer between SQL server and Crystal. So for people who wants to avoid cursor and make a fast function. Use this pattern.


CREATE FUNCTION dbo.ConcatSourceOfFinancing ( @Opportunity_ID varchar(32) )
RETURNS varchar(400) AS
BEGIN

DECLARE @value varchar(400)
SET @value = ''

SELECT @value = @value + Org.OrgName + ' ; '
FROM OppSalesTeam OST inner join org on (ost.org_id = org.org_id)
WHERE role = 'Financing' and OST.Opportunity_ID = @Opportunity_ID
ORDER BY Org.OrgName

if @value <> ''
set @value = left(@value, len(@value) - 2)

Return @value
END -- Function

Thanks everybody and specially gmmastros for this function!!