Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Row to Column conversion example in Oracle

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
Answers
-
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 aGROUP
BY
clause to return multiple values, yet the<span class="codeinlineitalic" style="font-style: italic;">pivot_clause</span>
does not contain an explicitGROUP
BY
clause. Instead, the<span class="codeinlineitalic" style="font-style: italic;">pivot_clause</span>
performs an implicitGROUP
BY
. 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>
.). -
Hi thanks for early reply, could you provide me the sample code please..
-
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
-
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.
-
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
-
let me check please..
-
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
-
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.
-
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,
-
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;