Learn how to a build a cloud-first strategyRegister Now

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

Function returning comma sepparated values (istead of crosstab)

Hi.

Would you please give me a workbale sample of the function which can simulate crosstab (pivot).

I need to send two parameters (group_id, type_id) to this function from the view and this function should return the list of comma sepparated values of that type_id.

So if there are three records with type_id values 'a', 'b', 'c' I need to convert them in one text field like 'a, b, c' from the view (query).

Thanks.
0
GoodName
Asked:
GoodName
1 Solution
 
ishandoCommented:
Something like this should do it:
create or replace function myCommaList (in_gid in integer, in_tid in integer)
  return varchar2
is
  rsetTab dbms_utility.uncl_Array;
  tablen  binary_integer;
  list    varchar2(4000);
  
begin
  select val bulk collect into rsetTab
  from table1
  where group_id = in_gid
  and type_id = in_tid;
  
  dbms_utility.table_to_comma(rsetTab, tablen, list);
  
  return list;
end myCommaList ;
/

Open in new window

0
 
GoodNameAuthor Commented:
Thanks for the help!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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