Marcelo wrote:
I have a master (teams) and a slave (players) table, say
Master table: IDTEAM TEAM --------------- 1 RIVER 2 BOCA 3 SAN LORENZO
Slave table
FKIDTEAM NAME -------------- 1 CARRIZO 1 FERRARI 1 CABRAL 2 ABONDANZIERI 2 RIQUELME 3 ...
Now I want a query to have this report:
TEAM PLAYERS -------------------------------------------------------- RIVER CARRIZO/FERRARI/CABRAL BOCA ABONDANZIERI/RIQUELME ...
All players concatenated in a field string. How can I do this?
"unordained" answers:
select team, list(name, '/') as players from master left join slave on
slave.fkidteam = master.idteam
group by team;
In 1.5, you could write a stored procedure. Approx. solution (not tested) below.
create procedure x returns (team varchar(50), name varchar(32000)) as
declare variable lt varchar(50);
declare variable t varchar(50);
declare variable n varchar(100);
begin
name = '';
for select team, name from master left join slave on slave.fkidteam =
master.idteam into :t, :n order by team do
begin
if (team != t) then
begin
lt = team;
suspend;
name = '';
end
team = t;
name = name || (case when name != '' then '/' else '') || n;
end
if (team != lt) then
suspend;
end
select team, name as players from x;
Marcelo adds:
Based on your idea I wrote this and it works! Thanks!
CREATE PROCEDURE LINEAEVENTOS
RETURNS (
ID Integer,
LINEA Varchar(32000) )
AS
DECLARE VARIABLE evento varchar(255);
declare variable lid int;
declare variable cid int;
BEGIN
LINEA = '';
select min(fkidcon) from EVENTOSXCON into lid;
for select fkidcon, nombre from EVENTOSXCON
left join eventos on (fkidevento=idevento) where not (nombre is null) order by fkidcon
into :cid, :evento
do
if (cid = lid) then
LINEA = LINEA || ' / ' || evento;
else
begin
ID = lid;
lid = cid;
suspend;
LINEA = evento;
end
END