sql query

Gor_Mahia

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.

Frank Kulash

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.

Frank Kulash

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

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.