getting column data into row as comma seperator

I have a table that has values as follows:
PersonID Degree
55 MD
55 Phd
55 RN
60 MD
60 Phd

I need a create a query that will give me output like this:

PersonID Degree
55 MD, Phd, RN
60 MD, Phd

Any ideas
KirtipurItagolAsked:
Who is Participating?
 
meluhkCommented:
Here is an example of how to do this without using a function:

SQL> desc pdegree
 Name                            Null?    Type
 ------------------------------- -------- ----
 PERSONID                                 NUMBER(3)
 DEGREE                                   VARCHAR2(6)

SQL> select * from pdegree;

  PERSONID DEGREE
---------- ------
        55 MD
        55 Phd
        55 RN
        60 MD
        60 Phd

SQL> l
  1  select personid,
  2     ltrim(sys_connect_by_path(degree,','),',') Degrees
  3  from (
  4     select personid, degree,
  5              row_number() over
  6                  (partition by personid order by degree) pd,
  7              count(*) over
  8                  (partition by personid) cnt
  9      from pdegree
 10        )
 11  where level = cnt
 12  start with pd = 1
 13* connect by prior personid = personid and prior pd = pd - 1;

  PERSONID DEGREES
---------- ---------------
        55 MD,Phd,RN
        60 MD,Phd
0
 
KirtipurItagolAuthor Commented:
Any solution guys
0
 
actonwangCommented:
You need to use user-defined aggregate function. Refer to this good information:

http://asktom.oracle.com/pls/ask/f?p=4950:8:11901907076081232097::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2196162600402

it has exact answer to your question!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
actonwangCommented:
it uses ODCIAggregate Interface to build aggregate function.

enjoy :)

Acton
0
 
ShweetaCommented:
Hi Kirti,

You could follow the following procedure:

1. Write a function say Func_Degree, that would take input as 'Person Id' and give output as a string of 'Degree'.

Inside the function, use the logic of cursor.

Cursor Cur_Degree is
  SELECT Degree FROM table1
  WHERE PersonId=<InputPersonId>

Define a variable to store output - say OutputDegree

Now, loop the cursor and attach each degree value to OutputDegree :

 LOOP                                
   FETCH Cur_Degree INTO degrees;
   EXIT WHEN Cur_Degree%NOTFOUND;  
         OutputDegree := OutputDegree || ',' || degrees;  
END LOOP;

Return OutputDegree.

2. Now write a simple sql statement, calling this function.. as

        SELECT A.PersonId, Func_Degree(A.PersonId)      
        FROM   (SELECT DISTINCT PersonId
                     FROM  table1) A;      
                                           
Regards,
Shweta
0
 
actonwangCommented:
Shweta,

       You method will incur a lot of fetchs. Why don't you just use user-defined agregate functions which is more efficient and generic?

Acton

     
0
 
actonwangCommented:
meluhk,

       this is really nice.

Acton
0
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.