OraQA

• Categories

  • Administration
  • APEX (HTMLDB)
  • Concepts
    • Architecture
  • E-Business Suite
  • General
  • Jdeveloper
  • PL/SQL
  • Question
  • Security
  • SQL
  • Tuning
  • XML

• Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

• Links

  • AskTom.oracle.com
  • Oracle doc search
  • Oracle Technology Network
  • OraNA - Oracle News Aggregator

Oracle News

Eno     Ename      Dept
10      Arvind     SW
10      Arvind     Prog
10      Ajay       SW

Eno     Ename      Dept
10      Arvind     SW/Prog
10      Ajay       SW

2 Responses to “How do I write this query?”

1. rjamya Says:
   April 19th, 2006 at 7:35 am

   head to http://asktom.oracle.com and search for stragg. load that in your db, then

   select eno, ename, stragg(dept) as dept
     from emp
     group by eno,ename
   /
   

   will give you what you want. Otherwise write your owne plsql function to do the concat.

   HTH
   Raj

2. shunya Says:
   June 12th, 2006 at 5:05 am

   Try this

   CREATE TABLE TMP_EMP
   (
   EMP_NO NUMBER,
   EMP_NAME VARCHAR2(20),
   DEPT_NAME VARCHAR2(20)
   );
   commit;

   create or replace function con_dept(vEMP_NO in number)
   Return varchar2
   is
   cursor cs is
   select distinct e.DEPT_NAME
   from tmp_emp e
   where e.EMP_NO = vEMP_NO;

   vDEPT varchar2(40);
   vTMP_DEPT varchar2(20);

   begin

   vDEPT:= ”;

   IF cs%ISOPEN THEN
   CLOSE cs;
   END IF;

   open cs;
   loop

   fetch cs into vTMP_DEPT;
   dbms_output.put_line(vTMP_DEPT);
   exit when cs%notfound;
   vDEPT:=vDEPT || ‘/’ || vTMP_DEPT;

   end loop;
   CLOSE cs;

   vDEPT := substr(vDEPT,2,length(vDEPT));

   return vDEPT;

   end;

   select distinct t.EMP_NO,t.EMP_NAME,con_dept(t.EMP_NO)
   from tmp_emp t;

Leave a Reply

You must be logged in to post a comment.