transpose rows into columns (oralce query)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: transpose rows into columns (oralce query)

  1. #1
    Chris Guest

    transpose rows into columns (oralce query)


    Can someone please tell me how to transpose one column of several rows into
    one column by one row using an oralce 8i query.

    example from this
    table A
    col1
    row1 A
    row2 B

    to this
    table B
    col1
    row1 A,B

    delimator can be any char

  2. #2
    Boris Milrud Guest

    Re: transpose rows into columns (oralce query)


    Chris,

    You can wrire a function, which will scan through tableA.col1 and construct
    a string, and then use it to insert one row in tableB:

    create or replace function BuildString (pDelimiter varchar2 := ',')
    return varchar2
    is
    vRetVal varchar2(32757);
    begin

    for vRec in (select col1 as MyColumn from tableA)
    loop
    vRetVal := vRetVal || pDelimiter || vRec.MyColumn;
    end loop;

    if vRetVal is not null
    then
    vRetVal := substr(vRetVal, length(pDelimiter) + 1);
    end if;

    return vRetVal;
    end BuildString;
    /

    Keep in mind that function could return a string up to 32757 characters long.
    Now you are ready to insert:

    insert into tableB (col1)
    select substr(BuildString, 1, N) from Dual;

    commit;

    where N is the width of tableB.col1 column

    Hope it helps.
    Boris.


    "Chris" <c.hoffmann@cqu.edu.au> wrote:
    >
    >Can someone please tell me how to transpose one column of several rows into
    >one column by one row using an oralce 8i query.
    >
    >example from this
    >table A
    > col1
    >row1 A
    >row2 B
    >
    >to this
    >table B
    > col1
    >row1 A,B
    >
    >delimator can be any char



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center