title: Merge and remove rows with blanks in oracle
body: <p>I have the data as below:</p>
  
  <pre><code>IDS_DEV Teradata IDS_DEV Data_Waive LEG_TX     Account Transaction No  XDR
  IDS_DEV Teradata IDS_DEV Data_Waive LEG_TX     Data Transaction    No  XDR
  IDS_DEV Teradata IDS_DEV Data_Waive LEG_TX                                
  IDS_DEV Teradata IDS_DEV Data_Waive LEG_TX     Job Transaction     No  XDR
  IDS_DEV Teradata IDS_DEV Data_Waive TRANS_AM   Account Transaction     DEG
  IDS_DEV Teradata IDS_DEV Data_Waive TRANS_AM                       Yes    
  IDS_DEV Teradata IDS_DEV Data_Waive TRA_EFF_DT Account Transaction     PDR
  IDS_DEV Teradata IDS_DEV Data_Waive TRA_EFF_DT                     No  PDR
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/uKSH8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uKSH8.png" alt="enter image description here"></a> </p>
  
  <p>Here the column HDFT can have multiple values, N_Identity can have Yes or No and Class will have one particular value.</p>
  
  <p>Now, for the same column group LEG_TX where there are multiple values for HDFT i just want all the rows except the blank row.
  Also, i want to combine the rows into one for Trans_AM and TRA_EFF_DT into one row.</p>
  
  <p>Below is the result i want:
  <a href="https://i.stack.imgur.com/gTLDw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gTLDw.png" alt="enter image description here"></a></p>
  
  <pre><code>DB  DBMS    Schema  Table   Column  HDFT    N_Identity  Class
  IDS_DEV Teradata    IDS_DEV Data_Waive  LEG_TX  Account Transaction No  XDR
  IDS_DEV Teradata    IDS_DEV Data_Waive  LEG_TX  Data Transaction    No  XDR
  IDS_DEV Teradata    IDS_DEV Data_Waive  LEG_TX  Job Transaction No  XDR
  IDS_DEV Teradata    IDS_DEV Data_Waive  TRANS_AM    Account Transaction Yes DEG
  IDS_DEV Teradata    IDS_DEV Data_Waive  TRA_EFF_DT  Account Transaction No  PDR
  </code></pre>
  
  <p>I am able to achieve the second case using Max(case..) but in doing so the first requirement is not getting met.</p>
  
  <p>Not adding the text data as the format is getting messed up.</p>
  
  <p>Thanks.</p>
  
  <p>[Updated]Adding another Sample:</p>
  
  <p><a href="https://i.stack.imgur.com/qvhUV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qvhUV.png" alt="enter image description here"></a></p>
  
  <p>Output:
  <a href="https://i.stack.imgur.com/bZVyy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bZVyy.png" alt="enter image description here"></a></p>
  
  <p>The N_Identity <strong>can be either</strong> Yes or No for a particular column.</p>