Function returning comma sepparated values (istead of crosstab)


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

Who is Participating?
ishandoConnect With a Mentor Commented:
Something like this should do it:
create or replace function myCommaList (in_gid in integer, in_tid in integer)
  return varchar2
  rsetTab dbms_utility.uncl_Array;
  tablen  binary_integer;
  list    varchar2(4000);
  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

GoodNameAuthor Commented:
Thanks for the help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.