Solved

Function returning comma sepparated values (istead of crosstab)

Posted on 2008-06-11
2
716 Views
Last Modified: 2013-12-19
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
Comment
Question by:GoodName
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 15

Accepted Solution

by:
ishando earned 500 total points
ID: 21765739
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
 

Author Closing Comment

by:GoodName
ID: 31466406
Thanks for the help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question