Turn column into a row

[soans]

Is there someway I can turn the following data
Index Column1 Column2
----- ------- -------
1 A B
1 C D

into

Index Column
1 ABCD

The self join gives a cartesian product.
Thanks

 

[rcurva]

If you want a single SQL solution, I don't have it with me. But you can do it using PL/SQL. Perhaps you know it already don't you?
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[soans]

Yes I knew it. But just wanted to make sure that I did not miss anything.
Thanks

 

[nme]

how about:

select a.index, min(a.column1 || a.column2 || b.column1 || b.column2)
from table a, table b
where a.index = b.index
and a.column1 <> b.column1
and a.column2 <> b.column2;

its not pretty - but hey, you didn't ask for beauty...

 

[rcurva]

If you are pretty sure that index can only have at most 2 distinct records, the above sql will work for you but if not, you have a problem there.

Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War

 

[ManjulaMadhu]

Soans,
If that works, then dont forget to add the group by clause

select a.index, min(a.column1 || a.column2 || b.column1 || b.column2)
from table a, table b
where a.index = b.index
and a.column1 <> b.column1
and a.column2 <> b.column2
group by a.index;

 

[soans]

Robbie is right. I've got anywhere from 1 to 11 records per index.

 

[soans]

Here's what I came up with. Is there better efficient and elegant code out there?
Thanks
----------------------------------------------------------
TYPE testCur IS REF CURSOR;
cur testCur;
stmt_str VARCHAR2(200);
testrecord table%ROWTYPE;
v_record varchar2(2000);
prev_index varchar2(25);
BEGIN
--sql query to execute
stmt_str := 'select * from table order by index';
OPEN cur FOR stmt_str;
LOOP
FETCH cur INTO testrecord;
IF prev_index<>testrecord.index THEN
--process the record and reset v_record for the next index
htp.p(prev_index);
htp.p(substr(v_record,2,length(v_record)));htp.p('<br>');
v_record:='';
ELSE
v_record:=v_record||'|'||testrecord.column1||'|'||testrecord.column2||'|'||testrecord.column3||'|';
END IF;
prev_index:=testrecord.index;
EXIT WHEN cur%NOTFOUND;
END LOOP;
--process the last record
htp.p(prev_index);
htp.p(substr(v_record,2,length(v_record)));htp.p('<br>');
CLOSE cur;
EXCEPTION
when others then
htp.p('message');
END;