DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Dynamic Column Generation

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    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.

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Many Many thanks hrishy.

    I will try this code and update you.

    Regards,
    Cheers!
    OraKid.

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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.

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    It should be

    PIPE ROW(l_pipe_row);

    regards
    Hrishy

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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
  •  


Click Here to Expand Forum to Full Width