Put 2 records in 1

[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.

Can you help me??

Thanks

 

[PHV]

Like this ?
SELECT MIN(ID), role, MIN(name) || ', ' || MAX(name), Opportunity
FROM OppSalesTeam
WHERE role = 'Financing' and Opportunity = '123456'
GROUP BY role, Opportunity

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cardi]

Thanks,

but if i want to regroup 3 (or 4 or 5...) records in 1. For 2 records, it's ok, but if I have 3-4-5, can I take the same pattern??

 

[PHV]

but if I have 3-4-5, can I take the same pattern?
No, not with straight SQL (AFAIK).
Depending of your RDBMS you may consider a Stored Procedure doing the concatenation.
Otherwise, do it by code in the presentation layer.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

depending on your rdbms, if it's mysql you can use the gorgeous GROUP_CONCAT function

r937.com | rudy.ca

 

[cardi]

I don't know how to figure it out. If I did a SELECT *... in my SP, how can I have all the records regroup in the same ???

Thanks

 

[PHV]

Do you know what a cursor is ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cardi]

yes... I think that I meet that in my college course!!! Do you i good web site to help me about that???

 

[cardi]

I tried this and it works well, but it fails if I have too much of records. I'm searching for a means for robust. This is my 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



Thanks

 

[gmmastros]

Try...

[COLOR=blue]CREATE[/color] [COLOR=#FF00FF]FUNCTION[/color] dbo.ConcatSourceOfFinancing ( @Opportunity_ID [COLOR=blue]varchar[/color](32) ) 
RETURNS [COLOR=blue]varchar[/color]([!]8000[/!]) AS
[COLOR=blue]BEGIN[/color] 

[COLOR=blue]DECLARE[/color] @value [COLOR=blue]varchar[/color]([!]8000[/!])
[COLOR=blue]SET[/color] @value = [COLOR=red]''[/color]

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

[COLOR=blue]if[/color] @value <> [COLOR=red]''[/color]
[COLOR=blue]set[/color] @value = [COLOR=#FF00FF]left[/color](@value, len(@value) - 2)

[COLOR=blue]Return[/color] @value
[COLOR=blue]END[/color] [COLOR=green]-- Function[/color]

-George

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

 

[r937]

could you guys take that discussion to forum183 please?

r937.com | rudy.ca