-
Dynamic Column Generation
Hi All,
I have a table like this and i need to display the data as in Point 2).
Can you guys please help? We need this for one of our application. Number of record per day 15000 records per day.
Point 1)
Transaction id Product id Serial number Reason
100 1 123456 ABCD
100 1 XYZR
100 1 222222
200 2 8768 GHFR
200 2 JAYEN
200 2 9876 AMRA
200 2 9999
The above data should be shown as shown below :
Point 2)
Transaction id Product id Serial number1 Serial number2 Serial number3 Serial number4 Reason1 Reason2 Reason3 Reason4
100 1 123456 222222 ABCD XYZR
200 2 8768 9876 9999 GHFR JAYEN AMRA
Thanks in Advance to all.
Cheers!
OraKid.
-
Hi
You can try this
Code:
create or replace type tbl_varchar as table of varchar2(4000);
CREATE or REPLACE FUNCTION pivot return tbl_varchar PIPELINED
as
l_poduct_id product_id%type := null;
l_pipe_row varchar2(4000);
begin
for row in (SELECT transaction_id,
poduct_id,
serial_number,
reason_code
from my_table) loop
if l_poduct_id is null then
l_pipe_row:=rpad(row.transaction_id,10)||rpad(row.product_id,10);
l_product_id:=row.product_id;
end if;
if row.product_id <> l_product_id then
pipe row(l_pipe_row);
l_product_id := row.product_id;
end if;
l_pipe_row := l_pipe_row || rpad(row.serail_number,10)||rpad(row.reason_code,10);
end loop;
pipe row(v_line);
return;
end pivot;
/
or try this
http://technology.amis.nld/blog/wp-c...ages/pivot.sql
regards
Hrishy
These sort of things are best done in ETL tool or reporting tools i think
-
Many Many thanks hrishy.
I will try this code and update you.
Regards,
Cheers!
OraKid.
-
hi hrishy
this is not working "pipe row(v_line);"... i am getting compilation error..
can you please help
this is the table and the code...
create table my_table (Transaction_id number, Product_id number, Serial_number number, Reason varchar2(100));
insert into my_table values (100, 1, 123456, 'ABCD');
insert into my_table values (100, 1, null, 'XYZR');
insert into my_table values (100, 1, 222222, null);
insert into my_table values (200, 2, 8768, 'GHFR');
insert into my_table values (200, 2, null, 'JAYEN');
insert into my_table values (200, 2, 9876, 'AMRA');
insert into my_table values (200, 2, 9999, null);
commit;
create or replace type tbl_varchar as table of varchar2(4000)
/
CREATE OR REPLACE FUNCTION pivot RETURN tbl_varchar
PIPELINED AS
l_product_id my_table.product_id%TYPE := NULL;
l_pipe_row VARCHAR2(4000);
BEGIN
FOR ROW IN (SELECT transaction_id
,product_id
,serial_number
,reason
FROM my_table)
LOOP
IF l_product_id IS NULL
THEN
l_pipe_row := rpad(ROW.transaction_id
,10) || rpad(ROW.product_id
,10);
l_product_id := ROW.product_id;
END IF;
IF ROW.product_id <> l_product_id
THEN
PIPE ROW(l_pipe_row);
l_product_id := ROW.product_id;
END IF;
l_pipe_row := l_pipe_row || rpad(ROW.serial_number
,10) || rpad(ROW.reason
,10);
END LOOP;
PIPE ROW(v_line); -- this part is failing.
RETURN;
END pivot;
/
Cheers!
OraKid.
-
Hi
It should be
PIPE ROW(l_pipe_row);
regards
Hrishy
-
Many thanks Hrishy.
I should have got that defect.
Many thanks for you support.
Cheers!
OraKid.
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
|