[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

Comma separated list for each ID

Hi,
  I have this table structure and data, which contains ID,Gender and Role.
  One ID can have multiple roles. I need to concatenate each of those roles into a comma separated list. for each ID.
   I have achieved this result through a recursive CTE, but it is expensive.
   I'd grateful if someone can kindly help me with the stored procedure for this.
 
 ID                  GENDER                 ROLE
 1                       F                          CASEHEAD
 1                       F                          REPORTER
 1                       F                          MALTREATOR
 2                       M                          OTHER MEMBER
 3                       M                         NEIGHBOR
 3                       M                         REPORTER

Open in new window


The result i'm trying to achieve.
 ID                    GENDER                    ROLE
 1                         F                            CASEHEAD,REPORTER,MALTREATOR
 2                         M                            OTHER MEMBER
 3                         M                            NEIGHBOR,REPORTER

Open in new window

 
0
pvsbandi
Asked:
pvsbandi
  • 2
  • 2
1 Solution
 
momi_sabagCommented:
this is not the exact syntax, but it should give you the general idea

create procedure myproc
begin
declare last_id int;
declare roles_list varchar(4000);
declare last_gender char(1);
declare curr_id int;
declare curr_gender char(1);
declare curr_role varchar(100);

create temprary table ret_vals (...)

declare c1 cursor as
select * from your_table order by id,role;

declare c2 cursor for return as select * from session.ret_vals;

open c1;
fetch c1 into curr_id, curr_gender, curr_role;

last_id = curr_id;
last_gender = curr_gender;

while (sqlcode = 0)
if curr_id = last_id then
   role_list = role_list || ',' || curr_role;
else
   insert into ret_vals values (last_id, last_gender, substr(roles_list, 2, 99999);
   roles_list = curr_role;
end if

last_id = curr_id;
last_gender = curr_gender;
fetch c1 into curr_id, curr_gender, curr_role;
end loop;

open c2;

end
0
 
pvsbandiAuthor Commented:
Thanks Momi!
   This is what i tried. But i don't get ID = 3 in the result at all. It stops with ID 2.
   What am i missing?

CREATE PROCEDURE ROLE_COMMA()
P1: BEGIN

declare last_id int;
declare roles_list varchar(4000) default '';
declare last_gender char(1);
declare curr_id int;
declare curr_gender char(1);
declare curr_role varchar(100);
declare sqlcode integer default 0;

declare c1 cursor with hold for
select * from co_role order by id,role;

declare global temporary table ret_vals (id integer,gender char(1),role varchar(50))
with replace;

open c1;
fetch c1 into curr_id, curr_gender, curr_role;



set last_id = curr_id;
set last_gender = curr_gender;

while (sqlcode=0) do
if curr_id = last_id then
 set  roles_list = roles_list || ',' || curr_role;
else
   insert into session.ret_vals values (last_id, last_gender,roles_list);
   set roles_list = curr_role;
end if;

set last_id = curr_id;
set last_gender = curr_gender;
fetch c1 into curr_id, curr_gender, curr_role;
end WHILE;
close c1;
p2:begin
 declare c2 cursor with return to client for
select * from session.ret_vals;
open c2;
end p2;

end p1
@

Open in new window

0
 
momi_sabagCommented:
yep
since you have 2 rows with id 3, they are not inserted
after the loop, you need to add something like this:

select 1 into dummy from session.ret_vals where id=last_id;

if (sqlcode =100)
insert into session.ret_vals values (last_id, last_gender,roles_list);
end if


this should take care of the last record
0
 
pvsbandiAuthor Commented:
Thank You!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now