CODEWITHSUNDEEP
sqlsql-serverteradata
user3901666
user3901666

Reputation: 409

Teradata/Sql_server query to merge consecutive rows for certain condition

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

Also, 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: 1

Views: 111

Answers (3)

Serg
Serg

Reputation: 22811

Compute N_Identity and Class separately from HDFT as it looks as their business rules are different. Join the results. HDFT subquery eliminates NULL if any non-NULL value is present. It builds on the Sql-server ordering which considers NULL as a minimum value. 

   SELECT nic.DB, nic.DBMS, nic.INST, nic.SCHEMA, nic.TABLE, nic.COLUMN
       , h.HDFT
       nic.N_Identity, nic.Class
   FROM
   (
        SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, MAX(N_Identity) as N_Identity, MAX(Class) as Class  
        FROM Table
        GROUP BY DB,DBMS,INST,SCHEMA,TABLE,COLUMN
   ) nic 
   JOIN   
   (
     SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, max(HDFT) HDFT
     FROM 
     (
         SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, HDFT
          , case when HDFT is null then 1
                 else dense_rank() over(
                   partition by DB,DBMS,INST,SCHEMA,TABLE,COLUMN
                   order by HDFT desc) 
             end rnk
         FROM Table
     ) t
     GROUP BY DB,DBMS,INST,SCHEMA,TABLE,COLUMN, rnk
   ) h ON h.DB = nic.DB AND h.DBMS = nic.DBMS AND h.INST = nic.INST AND h.SCHEMA = nic.SCHEMA AND h.TABLE = nic.TABLE AND h.COLUMN = nic.COLUMN

Upvotes: 1

dnoeth
dnoeth

Reputation: 60462

In Teradata @SalmanA's answer can be simplified using QUALIFY

SELECT DISTINCT DB, DBMS, INST, SCHEMA, "TABLE", "COLUMN",  HDFT,
       MAX(N_Identity)
       OVER (PARTITION BY DB, DBMS, INST, SCHEMA, "TABLE", "COLUMN") AS N_Identity,
       MAX(Class)
       OVER (PARTITION BY DB, DBMS, INST, SCHEMA, "TABLE", "COLUMN") as Class
FROM t
QALIFY HDFT IS NOT NULL
    OR MAX(HDFT)
       OVER (PARTITION BY DB, DBMS, INST, SCHEMA, "TABLE", "COLUMN") IS NULL

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below -

SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, MAX(HDFT) as HDFT, MAX(N_Identity) as N_Identity, MAX(Class) as Class   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
 GROUP BY DB,DBMS,INST,SCHEMA,TABLE,COLUMN

Upvotes: 0

Related Questions