| How to avoid duplicate Rows [message #2469] |
Thu, 18 July 2002 08:53  |
Miloud
Messages: 7
Registered: June 2002
|
Junior Member |
|
|
Hi,
My goal is how to avoid duplicate rows and replace them with one row by adding columns.
Her an example:
Table T with two columns C1, C2
Rows inserted:
R1: (1,a)
R2: (2,b)
R3: (2,c)
R4: (2,d)
R5: (1,x)
R6: (3,y)
My SQL query must return result like this:
1,a,x
2,b,c,d
3,y
It's obvious that when C1 is the same I add a new column in mq query.
Thank's for any suggestion
|
|
|
|
| Re: How to avoid duplicate Rows [message #2480 is a reply to message #2469] |
Fri, 19 July 2002 08:18  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
create a function.
something like this
SQL> create or replace function f1(n number) return varchar2 is
2 retval varchar2(200);
3 cursor c1 is select ename from emp where deptno=n;
4 begin
5 for crec in c1 loop
6 if c1%rowcount=1 then
7 retval:= crec.ename;
8 else
9 retval:=retval||','||crec.ename;
10 end if;
11 end loop;
12 return retval;
13 end;
14 /
Function created.
SQL> select deptno,f1(deptno) names from emp group by deptno;
DEPTNO NAMES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
