This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,783 Users
  • 2,269,776 Discussions
  • 7,916,827 Comments

Discussions

Row to Column conversion example in Oracle

994122
994122 Member Posts: 1,025
edited Jun 2, 2014 5:49AM in SQL & PL/SQL

Hi all,

source table:
c_bo_cntc(phone_type,phone_num)

source data exmple:
PH,123
PH,124
TPH,128
TPH,231
......
total 3000 records

TARGET table:

RR_CNTC(emp_phone_num,emp_tina_num)

TARGET data example:
123,128
124,231
......
It is a Row to Column conversion example in Oracle...
Also is it mandatory to use the aggregate function inside pivot()?
can some help me in this case.

Thanks

Tagged:
newenrbaManik

Answers

  • Moazzam
    Moazzam Senior Oracle Developer Lahore, PakistanMember Posts: 1,356 Silver Trophy
    Also is it mandatory to use the aggregate function inside pivot()?
    


    Yes, It is mandatory. Read below from Oracle documentation:


    The <span class="codeinlineitalic" style="font-style: italic;">pivot_clause</span> lets you write cross-tabulation queries that rotate rows into columns, aggregating data in the process of the rotation. The output of a pivot operation typically includes more columns and fewer rows than the starting data set. The <span class="codeinlineitalic" style="font-style: italic;">pivot_clause</span> performs the following steps:
    
    The <span class="codeinlineitalic" style="font-style: italic;">pivot_clause</span> computes the aggregation functions specified at the beginning of the clause. Aggregation functions must specify a GROUPBY clause to return multiple values, yet the <span class="codeinlineitalic" style="font-style: italic;">pivot_clause</span> does not contain an explicit GROUPBY clause. Instead, the <span class="codeinlineitalic" style="font-style: italic;">pivot_clause</span> performs an implicit GROUPBY. The implicit grouping is based on all the columns not referred to in the <span class="codeinlineitalic" style="font-style: italic;">pivot_clause</span>, along with the set of values specified in the<span class="codeinlineitalic" style="font-style: italic;">pivot_in_clause</span>.).
    

    SELECT

    Moazzam
  • 994122
    994122 Member Posts: 1,025

    Hi thanks for early reply, could you provide me the sample code please..

  • newenrba
    newenrba BanjalukaMember Posts: 133

    WITH

       T

    AS

    (

       SELECT

          DEPTNO

       FROM

          EMP

    )

    SELECT

       *

    FROM

       T

    PIVOT

    (

       COUNT(*)

       FOR

          (DEPTNO)

       IN

          (10,20,30,40)

    );

    from second site searching "pivot in oracle" in google

    newenrba
  • Moazzam
    Moazzam Senior Oracle Developer Lahore, PakistanMember Posts: 1,356 Silver Trophy

    The link I provided for Oracle documentation contains PIVOT examples e.g.

    CREATE TABLE pivot_table AS
    SELECT * FROM
    (SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM orders)
    PIVOT
    (SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet));
    
    SELECT * FROM pivot_table ORDER BY year;
    
      YEAR STORE INTERNET
    ---------- ---------- ----------
      1990 61655.7
      1996 5546.6
      1997 310
      1998  309929.8   100056.6
      1999  1274078.8  1271019.5
      2000  252108.3   393349.4
    
    6 rows selected.
    

    SELECT

  • Robert Angel
    Robert Angel Director - Angel Applications Newcastle Upon TyneMember Posts: 4,535 Bronze Crown

    Looking at your example, if it is this 'small', have you looked at string aggregation instead?

    https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:15637744429336

    newenrbaManikRobert Angel
  • 994122
    994122 Member Posts: 1,025

    let me check please..

  • 994122
    994122 Member Posts: 1,025

    but in my case if i use aggregate function inside pivot() it will not give me a desired o/p and i also know without aggregate function pivot() will not work...anything else please help me.

    Thanks

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited May 30, 2014 9:00AM

    Hi,

    994122 wrote:
    
    but in my case if i use aggregate function inside pivot() it will not give me a desired o/p
     

    Why do you think that?

    Whenever you have a problem, post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data.

    Show what you've tried, based on the replies already given.

    and i also know without aggregate function pivot() will not work.... 
     

    That's right.  Aggregation means each row of output that can be based on more than 1 row of input.  Isn't that exactly what you want?  So use aggregation.

  • Raj Nath
    Raj Nath Dubai, UAEMember Posts: 394 Blue Ribbon

    Hi use this

    SELECT MAX(CASE WHEN phone_type='PH' THEN PHONE_NUM ELSE NULL END) COL1,

           MAX(CASE WHEN phone_type='TPH' THEN PHONE_NUM ELSE NULL END) COL2

    FROM (SELECT PHONE_TYPE, PHONE_NUM, ROW_NUMBER() OVER(PARTITION BY PHONE_TYPE ORDER BY PHONE_NUM) RN

    FROM c_bo_cntc)

    GROUP BY RN;

    But please do some basic search, i think same type of lot of solution is given in this forum and widely available on internet also, request you to please google it before putting the same type of question,

    Raj Nath
  • 994122
    994122 Member Posts: 1,025
    edited Jun 2, 2014 5:49AM

    Hi raj,thanks

    can you please help how to implement the same code in the below procedure plz!!!

    CREATE OR replace PROCEDURE Proc_rr_contact

    AS

    BEGIN

        INSERT INTO rr_contact

                    (source_system,

                     role_type,

                     emp_code,

                     emp_email_addr,

                     emp_first_name,

                     emp_last_name,

                     emp_phone_number,

                     emp_tina,

                     emp_site,

                     eduid,

                     emp_salutation,

                     emp_activity_status_code,

                     ts_code,

                     ts_descr,

                     ts_activity_status_code,

                     ts_parent_company_org_code)

        SELECT Trim (a1.last_rowid_system) AS source_system,

               b1.role_cd                  AS role_type,

               Trim (a2.pkey_src_object)   AS emp_code,

               a1.email_addr               AS emp_email_addr,

               a1.fst_nme                  AS emp_first_name,

               a1.last_nme                 AS emp_last_name,

               CASE

                 WHEN c1.phone_typ_cd = 'PH' THEN c1.phone_nbr

               END AS emp_phone_number,

               CASE

                 WHEN c1.phone_typ_cd = 'TPH' THEN c1.phone_nbr

               END AS emp_tina,

               a1.site                     AS emp_site,

               a1.ed_uid                   AS eduid,

               a1.emp_salutation           AS emp_salutation,

               a1.activity_status_cd       AS emp_activity_status_code,

               b1.ts_cd                    AS ts_code,

               b1.descr                    AS ts_descr,

               b1.activity_status_code     AS ts_activity_status_code,

               b1.par_com_org_cd           AS ts_parent_company_org_code

        FROM   cmx_ors2.c_bo_cntc a1,

               cmx_ors2.c_bo_cntc_xref a2,

               cmx_ors2.c_bo_cntc_role b1,

               cmx_ors2.c_bo_cntc_phone c1

        WHERE  a1.rowid_object = a2.rowid_object

               AND a1.rowid_object = b1.cntc_id(+)

               AND a1.rowid_object = c1.cntc_id(+);

        COMMIT;

    END;

    This is your code:

    SELECT Max(CASE

                 WHEN phone_type = 'PH' THEN phone_num

                 ELSE NULL

               END) COL1,

           Max(CASE

                 WHEN phone_type = 'TPH' THEN phone_num

                 ELSE NULL

               END) COL2

    FROM   (SELECT phone_type,

                   phone_num,

                   Row_number() over( PARTITION BY phone_type ORDER BY phone_num) RN

            FROM   c_bo_cntc)

    GROUP  BY rn;

This discussion has been closed.