-
How to concatenate many rows to be one row?
Hi,
How can I do if I concatenate two rows or many rows become one row in the same column with different values (CHAR or VARCHAR2). It's possible to do that?
Ex:
Column A
---------
ABCD
EFGH
....
to become
Column A
--------
ABCDEFGH....
Anybody can help me, please...
I'ld very appreciated.
Thanks,
Ching
ching
-
use a procedure with a cursor.
set serveroutput on;
DECLARE
v_string_new varchar2(200);
cursor cGet_String is
select column_A
from table_A;
-- use a where clause here to limit the return
BEGIN
v_string_new := '';
FOR v_string IN cGet_String LOOP
v_string_new := v_string_new || v_string.column_A;
END LOOP;
-- use an insert statement here to throw in a table if you like
dbms_output.put_line (v_string_new);
END;
/
- Cookies
-
How about
select ename || ename from emp;
you can filter the result by using where clause
-
No, you'll have to do a lot more than filter it. The string concatenation via PL/SQL is a good way to accomplish this. Doing a select whatever||whatever from where_ever deals with one record at a time; hence, the useless results as shown below.
Code:
SQL> select ename||ename from emp;
ENAME||ENAME
--------------------
SMITHSMITH
ALLENALLEN
WARDWARD
JONESJONES
MARTINMARTIN
BLAKEBLAKE
CLARKCLARK
SCOTTSCOTT
KINGKING
TURNERTURNER
ADAMSADAMS
JAMESJAMES
FORDFORD
MILLERMILLER
14 rows selected.
-
How to make this work?
I tried this procedure. It didn't work. It retuen (1) NOT (2)
(1) Column_a (2) Column_a
--------- ---------
ABC ABCDEF
DEF
How to manipulate this? Please give more tips. Thanks
Frank
-
Try this method ...
drop table my_table;
create table my_table (my_section number,my_piece number,my_text varchar2(4));
insert into my_table values (1,1,'ABCD');
insert into my_table values (1,2,'E');
insert into my_table values (1,3,'FGH');
insert into my_table values (1,4,'IJK');
insert into my_table values (2,1,'LM');
insert into my_table values (2,2,'NOP');
select my_section,all_text from
(
select
my_section,
my_piece,
my_text||
lead(my_text,1) over (partition by my_section order by my_piece)||
lead(my_text,2) over (partition by my_section order by my_piece)||
lead(my_text,3) over (partition by my_section order by my_piece)||
lead(my_text,4) over (partition by my_section order by my_piece)||
lead(my_text,5) over (partition by my_section order by my_piece)||
lead(my_text,6) over (partition by my_section order by my_piece)||
lead(my_text,7) over (partition by my_section order by my_piece) all_text,
first_value(my_piece) over(partition by my_section) first_piece
from
my_table
)
where my_piece = first_piece
/
MY_SECTION ALL_TEXT
---------- --------------------------------
1 ABCDEFGHIJK
2 LMNOP
Like most SQL-based methodologies you have to code an upper limit on the number of strings you are going to concatenate, but it has the advantages of ...
i) not requiring PL/SQL
ii) being v. fast.
-
Wow, Dave. After Tim Robbins and Susan Sarandon, you are my hero.
-
-
Actually, if you like that, what about ...
create or replace type AggratinateImpl as object
(
concat_string varchar2(4000),
static function ODCIAggregateInitialize(sctx in out AggratinateImpl) return number,
member function ODCIAggregateIterate(self in out AggratinateImpl, value in varchar2) return number,
member function ODCIAggregateTerminate(self in AggratinateImpl, returnValue out varchar2,flags in number) return number,
member function ODCIAggregateMerge(self in out AggratinateImpl,ctx2 in AggratinateImpl) return number
);
create or replace type body AggratinateImpl is
static function ODCIAggregateInitialize(sctx IN OUT AggratinateImpl)
return number is
begin
sctx := AggratinateImpl('');
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT AggratinateImpl, value IN varchar2)
return number is
begin
self.concat_string := self.concat_string||value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN AggratinateImpl, returnValue OUT
varchar2, flags IN number) return number is
begin
returnValue := self.concat_string;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self in out AggratinateImpl,ctx2 in AggratinateImpl) return number
is
begin
self.concat_string := self.concat_string||ctx2.concat_string;
return ODCIConst.Success;
end;
end;
/
create or replace function Aggratinate(input varchar2) return varchar2
aggregate using AggratinateImpl;
... then, using the same my_table as before ...
select my_section,aggratinate(my_text)
from my_table group by my_section;
... or ...
select my_section,aggratinate(my_text)
from (select my_section,my_text from my_table order by my_piece desc)
group by my_section
/
you need the create type privilege,and might blow out the varchar2(4000), but hey, it's pretty elegant stuff though i say so myself.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|