Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 240 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 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
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 443 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
I have a table that is having data shown in table1. The required output is given in table 2

table1:
table2: Required output
Answers
-
Hi, @Pwc Mustajab
Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).
Taking 1 column on N rows and displaying it as N columns on 1 row is called Pivoting. Starting in Oracle 11, you can use the built-in
SELECT ... PIVOT
feature. In any version, you can use GROUP BY with CASE or DECODE.In any query, you need to know the exact number of columns in the result set and their names before you can run the query. (In this case, that means you need to know that there are three distinct values of child_rfq and that their values are '51-1', '51-2' and '51-3'.) If you want either of those things to depend on the data that is actually in the table, then you need dynamic SQL.
-
If you are able to use PIVOT you can use something like this.
Also you need to know all the column in advance.
with t(Parentrfq,childrfq,item,qty,price,supplier) as ( select 51,'51-1','X',10,10,'A' from dual union all select 51,'51-1','Y',20,30,'B' from dual union all select 51,'51-2','X',10,12,'A' from dual union all select 51,'51-3','X',10,14,'B' from dual union all select 51,'51-3','Y',20,13,'A' from dual ) , t1 as ( select parentrfq, item, qty, price, supplier||' '||childrfq as p_child from t ) SELECT * FROM t1 PIVOT ( MAX ( price ) FOR p_child IN ( 'A 51-1' , 'A 51-2' , 'A 51-3' , 'B 51-1' , 'B 51-2' , 'B 51-3' ) );
-
Hi, @Pwc Mustajab
Is the suggestion above doing what you want? You don't need a sub-query; you can get the same results this way:
SELECT * FROM t PIVOT ( MIN (price) FOR (supplier, childrfq) IN ( ('A', '51-1') AS a_51_1 , ('A', '51-2') AS a_51_2 , ('A', '51-3') AS a_51_3 , ('B', '51-1') AS b_51_1 , ('B', '51-2') AS b_51_2 , ('B', '51-3') AS b_51_3 ) ) ORDER BY parentrfq, item, qty ;