Forum Stats

  • 3,759,238 Users
  • 2,251,515 Discussions
  • 7,870,550 Comments

Discussions

sql query

Gor_Mahia
Gor_Mahia Member Posts: 1,103 Bronze Badge

All,

ive a table with the below details which can be filtered based on the foreign ket attribute.


based on the search value i want to display the returned rows as a set of columns in one row as shown below,


  only for the available phase value for no available phase return NULL.

thank you.

Tagged:
Hub Tijhuis

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    Accepted Answer

    Hi, @Gor_Mahia

    Displaying data from N rows as N columns on the same row is called Pivoting. Here's one way to do it:

    WITH  data_to_pivot  AS
    (
      SELECT foreign_key, phase, startdate, finishdate
      FROM   table_x
    -- WHERE  ...		-- If needed
    )
    SELECT   *
    FROM	 data_to_pivot
    PIVOT	 ( MIN (startdate)	AS startdate
    	 , MIN (finishdate)	AS finishdate
    	 FOR phase  IN  ( 'CloseOut'	AS closeout
    	    	        , 'Prototype'	AS prototype
    			, 'Initiation'	AS initiation
    			)
    	 )
    ORDER BY foreign_key
    ;
    

    Of course, I can't test it without any sample data.

    Is the combination (foreign_key, phase) unique? If not, post an example with duplicates.

    If your table only includes the four columns you mentioned, then you don't need the sub-query.

    The result set from the query above has columns with names with the phase first, e.g. closeout_startdate. If you want the original column name first (e.g. startdate_closeout) then use column aliases in the SELECT clause.

    Gor_Mahia

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond

    Hi, @Gor_Mahia

    Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, 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 you complete Oracle version (e.g. 18.4.0.0.0).

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    Accepted Answer

    Hi, @Gor_Mahia

    Displaying data from N rows as N columns on the same row is called Pivoting. Here's one way to do it:

    WITH  data_to_pivot  AS
    (
      SELECT foreign_key, phase, startdate, finishdate
      FROM   table_x
    -- WHERE  ...		-- If needed
    )
    SELECT   *
    FROM	 data_to_pivot
    PIVOT	 ( MIN (startdate)	AS startdate
    	 , MIN (finishdate)	AS finishdate
    	 FOR phase  IN  ( 'CloseOut'	AS closeout
    	    	        , 'Prototype'	AS prototype
    			, 'Initiation'	AS initiation
    			)
    	 )
    ORDER BY foreign_key
    ;
    

    Of course, I can't test it without any sample data.

    Is the combination (foreign_key, phase) unique? If not, post an example with duplicates.

    If your table only includes the four columns you mentioned, then you don't need the sub-query.

    The result set from the query above has columns with names with the phase first, e.g. closeout_startdate. If you want the original column name first (e.g. startdate_closeout) then use column aliases in the SELECT clause.

    Gor_Mahia