Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006

    Unanswered: Transpose Columns to Rows

    Hi all,

    Ive looked everywhere for a decent solution to this.

    I want to be able to type

    select * from table(col2row(cursor(select * from mytable)));

    Any ideas anyone?

    has to be generic to any query.

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    Have you seen Tom Kyte's print_table procedure? It may give you some ideas if nothing else.

  3. #3
    Join Date
    Mar 2006
    Ive looked at the example.

    It accepts a query as a string, which is very easy to assign to a ref-cursor, describe the columns, and select the results using dbms_sql.

    However, I want to use a ref-cursor.

    all I could do with that example is:-

    select * from table(col2rows('select * from tab1 where 1=2'));

    but when the query gets complicated and uses many single quotes, it gets out of hand pretty quick.

  4. #4
    Join Date
    May 2005

    I've used this answer by Tom Kyte

    select decode( r, 1, to_char(deptno), 2, dname, 3, loc )
    2 from dept, (select rownum r from all_objects where rownum <= 3 )
    3* where deptno = 10

  5. #5
    Join Date
    Mar 2006
    Thx, but I can write bespoke solutions quite happily. I was rather hoping for a "catch all" solution.

    I have instead opted to create a pipelined table function, which accepts a weak refcursor, and you can use a select statement to generate a delimited line of text, which will then be parsed and converted into rows by the pipelined function.


    select * from table(pkg_tools.pipe2rows(cursor(select a || '|' || b from tab1)));
    select * from table(pkg_tools.pipe2rows('a|bb|ccc'));

    both return multiple rows.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts