Row to Column conversion example in Oracle

994122

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

Moazzam

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

994122

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

newenrba

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

Moazzam

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

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

994122

let me check please..

994122

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

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

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,

994122

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;