CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mani
Mani

Reputation: 741

Combine rows ignoring null values

I need to combine the rows which has null values and keep only the unique combination.

enter image description here

Output:

enter image description here

Query: I tried to take the maximum per group but since it considers null value it is not combining. Can you please suggest how to combine values.

SELECT SALES_ORG 
              ,MAX(MATERIAL) OVER (PARTITION BY  SALES_ORG,MATERIAL ORDER BY MATERIAL) as MATERIAL
              ,MAX(MGROUP) OVER (PARTITION BY SALES_ORG,MATERIAL,MGROUP ORDER BY MGROUP  ) as MGROUP  
              ,MAX(SERVICE) OVER (PARTITION BY SALES_ORG,MATERIAL,MGROUP,SERVICE ORDER BY SERVICE  ) as SERVICE 
              
    FROM SALES

Upvotes: 0

Views: 1374

Answers (1)

MT0
MT0

Reputation: 168326

You can get your output by aggregating by the first 3 columns and taking the maximum of the service column:

SELECT sales_org,
       material,
       mgroup,
       MAX(service) AS service
FROM   table_name
GROUP BY
       sales_org,
       material,
       mgroup;

Which, for the sample data:

CREATE TABLE table_name (sales_org, material, mgroup, service) AS
SELECT 'XS13', NULL, 10,   'ZSERV1' FROM DUAL UNION ALL
SELECT 'XS13', 'M1', 10,   'ZSERV1' FROM DUAL UNION ALL
SELECT 'XS13', NULL, 10,   NULL     FROM DUAL UNION ALL
SELECT 'XS13', NULL, 10,   'ZSERV2' FROM DUAL UNION ALL
SELECT 'XS13', NULL, 11,   'ZSERV2' FROM DUAL UNION ALL
SELECT 'XS13', 'M2', 12,   'ZSERV2' FROM DUAL UNION ALL
SELECT 'XS14', 'M2', NULL, NULL     FROM DUAL;

Gives the output:

SALES_ORG MATERIAL MGROUP SERVICE
XS13 null 10 ZSERV2
XS13 M1 10 ZSERV1
XS13 null 11 ZSERV2
XS13 M2 12 ZSERV2
XS14 M2 null null

Update:

From comments:

I have to first check MGROUP/SERVICE/MATERIAL if this combination is present then ignore all the other with the same value/combination. If only SERVICE/MATERIAL is present Then ignore all the otehr below combination.

You can use a hierarchical query to connect each row to more specific rows and then find the leaf rows:

SELECT DISTINCT
       sales_org,
       material,
       mgroup,
       service
FROM   table_name
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY NOCYCLE
      (PRIOR sales_org = sales_org OR PRIOR sales_org IS NULL)
  AND (PRIOR material  = material  OR PRIOR material  IS NULL)
  AND (PRIOR mgroup    = mgroup    OR PRIOR mgroup    IS NULL)
  AND (PRIOR service   = service   OR PRIOR service   IS NULL);

Which outputs the same as above.

db<>fiddle here

Upvotes: 2

Related Questions