CODEWITHSUNDEEP
sqloracle-databaseanalytic-functions
user3901666
user3901666

Reputation: 409

Query to Merge subsequent rows in Oracle/Teradata

I have a table with data as below

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t                      HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  GID     
IDS TD   SBD    IDS   Data_Val  cust_t  Phone       
IDS TD   SBD    IDS   Data_Val  cust_t  Account     
IDS TD   SBD    IDS   Data_Val  cust_t  Visa        
IDS TD   SBD    IDS   Data_Val  cust_t  Mail        
IDS TD   SBD    IDS   Data_Val  cust_t  Email       
IDS TD   SBD    IDS   Data_Val  cust_t  Login   Yes 
TDS TD   FDT    TDS   Expense   Exp_t   Name                 LOW
TDS TD   FDT    TDS   Expense   Exp_t           Yes

I want the output as below:

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t  GID     Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Phone   Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Account Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Visa    Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Mail    Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Email   Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Login   Yes         HIGH
TDS TD   FDT    TDS   Expense   Exp_t   Name    Yes         LOW

N_identity will have either Yes or No value for a particular column.So far i have tried to use the below query but it's not giving me the desired result:

SELECT * FROM
(
   SELECT * FROM
   (
        SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, MAX(HDFT) as HDFT, MAX(N_Identity) as N_Identity, MAX(Class) as Class  
        FROM Table
        GROUP BY DB,DBMS,INST,SCHEMA,TABLE,COLUMN
   )a 

  UNION

  SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, HDFT, N_Identity, Class FROM Table

)b

WHERE HDFT IS NOT NULL
AND N_Identity IS NOT NULL
AND Class IS NOT NULL

Updated requirement: The HDFT value can be null and below is one scenario:

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t          No          INT
IDS TD   SBD    IDS   Data_Val  cust_t                      INT
IDS TD   SBD    IDS   Data_Val  cust_t          No

Result Expected:

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t          No          INT

Upvotes: 0

Views: 75

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

I suppose you need nvl() and first_value() analytic functions only by considering to filter hdft is not null at the last step (after subquery operation is finished by those functions ) :

with tab2 as
(    
select db, dbms, inst, schema, "table", "column", hdft,
       first_value(class) over (partition by db) as class, 
       nvl(N_Identity,'Yes') as N_Identity 
  from tab                    
)
select * from tab2 where hdft is not null;

Demo

P.S. Avoid using preserved keywords for naming of table or columns such as table, column.

Upvotes: 2

Isuri Subasinghe
Isuri Subasinghe

Reputation: 147

You can use first_value function like this:

with selection as
(    
select db, dbms, inst, scheme, table1, column1, hdft,
       first_value(Class1) over (partition by db order by Class1) as "class1", 
       first_value(N_Identity) over (partition by db order by N_Identity)  as "N_Identity"
  from Y                 
)
select * from selection where hdft is not null;

Output

Upvotes: 0

user7229222
user7229222

Reputation: 

select db,dbms,inst,schema,table,column,hdft,
case when N_identity>0 then N_Identity else 'Yes' end as N_identity
,case when class>0 then class 
when column='cust_t' then 'HIGH'
when column='Exp_t' then 'LOW' end as Class
from table
where hdft>0

Upvotes: 0

Related Questions