Duplicate rows [message #2470] |
Thu, 18 July 2002 08:55 |
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: Duplicate rows [message #2517 is a reply to message #2470] |
Mon, 22 July 2002 22:34 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
You could do sth like this:
1 declare
2 cursor col1
3 is
4 select distinct(col_1) col_1
5 from temp_mhe;
6 cursor col2(p_col_1 number)
7 is
8 select col_2
9 from temp_mhe
10 where col_1 = p_col_1;
11 x varchar2(20);
12 begin
13 for rec_1 in col1
14 loop
15 x := to_char(rec_1.col_1);
16 for rec_2 in col2(rec_1.col_1)
17 loop
18 x := x||', '||rec_2.col_2;
19 end loop;
20 dbms_output.put_line(x);
21 x := NULL;
22 end loop;
23* end;
SQL> /
1, a, x
2, b, c, d
3, y
PL/SQL procedure successfully completed.
MHE
|
|
|