Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: how to convert rows to columns dynamically

    I'm very inexperienced with oracle, but a lot of times I think something is impossible I find out later that it is not only possible but common so here goes my crazy question.

    Is there a common way to convert rows to columns in a view or something dynamic like that? Example:

    Table that exist today

    EMPLOYEE TABLE

    empid pay_type_id pay_factor
    101 A 10
    111 A 15
    112 B 3000
    101 C 250
    111 C 250

    PAY_TYPES

    pay_type_id pay_type_code description
    A var variable pay
    B com commision
    C car car allowance
    D bon bonus

    View I would like to create

    empid var com car bon
    101 10 null 250 null
    111 15 null 250 null
    112 null 3000 null null

    So basically I need the employee table and the pay_types table joined and then switch the pay_type_id and pay_factor switched from rows to columns using the pay_type code as a column heading. Is this possible in oracle 9.2.0.1? Keep in mind however many rows show up in pay_types is the number of columns I need in the view even if no one has a row with that pay_type.

    -sharief

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246

    Re: how to convert rows to columns dynamically

    I saved the following from a posting 1-1/2 years ago. I was at an Oracle conference and someone had problem solved another way, which I thought was a function in SQL*Plus 9.2. Good luck.


    From: Vivek Verma 03-Aug-02 08:09
    Subject: Re : Return multiple rows as a single row with multiple columns

    You can do this by using the following select statement:

    select abc.ts,abc1.ts1,abc2.ts2 from
    (select distinct tablespace_name ts
    from dba_segments
    where rownum < 2) abc,
    (select distinct tablespace_name ts1
    from dba_segments
    where tablespace_name <> (select distinct tablespace_name
    from dba_segments
    where rownum < 2)
    and rownum < 2) abc1,
    (select distinct tablespace_name ts2
    from dba_segments
    where tablespace_name <> (select distinct tablespace_name
    from dba_segments
    where tablespace_name <> (
    select distinct tablespace_name
    from dba_segments
    where rownum < 2)
    and rownum < 2)
    and tablespace_name <> ( select distinct tablespace_name from dba_segments where rownum < 2)
    and rownum < 2) abc2



    Regards
    Vivek
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: how to convert rows to columns dynamically

    converting rows to columns is called pivot query. You can find a lot information about this topic on the internet.

    In order to achieve it, by the time you create sql statement you have to know the maximum distinct values of the rows you want to convert to columns. If rows are not static, you might have to use dynamic sql in order to avoid writing the query again and again.

    In Tom Kyte's Expert one-on-one, he provided a generic solution by using dynamic sql. You can find relevant info from his site asktom.oracle.com by searching 'pivot'. If you need the code, i have it and let me know.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    PIVOT table

    goto http://asktom.oracle.com
    search under pivot table. you'll find some great stuff.

    you can also search under this forum for some things I
    and others posted as well but eventually it will lead you to
    the asktom website again.

    here is a simple example where the first query is with no pivot
    and the second with a pivot:
    PHP Code:
    11:21:04 stagedemo@kop1SELECT org_idcust_id FROM CUSTOMER
    11
    :21:12   2  WHERE org_id IN ('OL','FTEST');

    ORG_ID   CUST_ID
    -------- ----------
    FTEST    6060
    FTEST    6061
    FTEST    6062
    FTEST    6065
    FTEST    6066
    FTEST    6067
    FTEST    6068
    OL       OLA123456

    8 rows selected
    .

    Elapsed00:00:00.00
    11
    :21:13 stagedemo@kop1SELECT 
    11
    :21:32   2  MAXDECODE(org_id'OL'cust_idNULL )) "OL",
    11:21:32   3  MAXDECODE(org_id'FTEST'cust_idNULL )) "FTEST"
    11:21:32   4  FROM CUSTOMER
    11
    :21:32   5  WHERE org_id IN ('OL','FTEST')
    11:21:32   6  GROUP BY cust_id;

    OL              FTEST
    --------------- ---------------
                    
    6060
                    6061
                    6062
                    6065
                    6066
                    6067
                    6068
    OLA123456

    8 rows selected

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2004
    Posts
    7
    Perfect!!! That worked exactly the way I wanted it to. Thank you very much!!!

    -sharief

Posting Permissions

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