?
Solved

getting column data into row as comma seperator

Posted on 2006-04-20
7
Medium Priority
?
410 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:KirtipurItagol
7 Comments
 

Author Comment

by:KirtipurItagol
ID: 16504361
Any solution guys
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 200 total points
ID: 16504393
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16504398
it uses ODCIAggregate Interface to build aggregate function.

enjoy :)

Acton
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Assisted Solution

by:Shweeta
Shweeta earned 200 total points
ID: 16505080
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16506043
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
 
LVL 4

Accepted Solution

by:
meluhk earned 1600 total points
ID: 16506964
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16507248
meluhk,

       this is really nice.

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

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

850 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