Forum Stats

  • 3,828,095 Users
  • 2,260,862 Discussions
  • 7,897,473 Comments

Discussions

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

Pwc Mustajab
Pwc Mustajab Member Posts: 1 Green Ribbon

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,083 Red Diamond

    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
    alvinder Member Posts: 435 Silver Badge

    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
    Frank Kulash Member, Moderator Posts: 42,083 Red Diamond

    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
    ;
    


    alvinder