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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KirtipurItagolAuthor Commented:
Any solution guys
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!
actonwangCommented:
it uses ODCIAggregate Interface to build aggregate function.

enjoy :)

Acton
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

     
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
actonwangCommented:
meluhk,

       this is really nice.

Acton
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.