I have a table that is having data shown in table1. The required output is given in table 2

Pwc Mustajab

table1:

table2: Required output

Frank Kulash

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.

alvinder

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' )
  );

Frank Kulash

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
;