CODEWITHSUNDEEP
oracle-database
sqlpractice
sqlpractice

Reputation: 141

Combine Multiple Column values into one Row

I have the data like below where I want to combine the rows having null values with not null values.

ID    ColA    ColB      ColC     ColD
1     A1      B1        null     null
1     A2      B2        null     null
1     null    B3        C1       D1
2     A3      B3        null     null
3     null    null      C3       D3

Expected Result:

ID  ColA   ColB    ColC     ColD
1   A1      B1     C1       D1
1   A2      B2     C1       D1
2   A3      B3     null     null
3   null    null   C3       D3

Basically for a single ID multiple rows (different COLA)can exist but only one row with null ColA.

ColC and ColD will always be null for the rows having not null ColA.

I want to merge the ColB,ColC and ColD values of the row having null ColA with all the rows having not null ColA.

Upvotes: 1

Views: 104

Answers (2)

krokodilko
krokodilko

Reputation: 36137

One way is to use two correlated subqueries:

SELECT ID,ColA,ColB,
       (SELECT ColC FROM table y 
        WHERE x.id = y.id AND ColA IS NULL) As ColC,
       (SELECT ColD FROM table y 
        WHERE x.id = y.id AND ColA IS NULL) As ColD
FROM table x
WHERE ColA IS NOT NULL

Another way is to use a self join

SELECT x.ID,x.ColA,x.ColB,
       y.ColC,y.ColD
FROM table x
JOIN table y
ON x.ID = y.ID AND x.ColA IS NOT NULL AND y.ColA IS NULL

EDIT

If for a particular ID only one row with not null ColA value

I guess you mean that there is no row with ColA = NULL at all for particular id.
In this case the first query will work, and the second query must use LEFT JOIN:

SELECT x.ID,x.ColA,x.ColB,
       y.ColC,y.ColD
FROM table x
LEFT JOIN table y
ON x.ID = y.ID AND x.ColA IS NOT NULL AND y.ColA IS NULL

or a row having only null ColA

In this case the condition must be extended with NOT EXISTS subquery:

SELECT ID,ColA,ColB,
       (SELECT ColC FROM table y 
        WHERE x.id = y.id AND ColA IS NULL) As ColC,
       (SELECT ColD FROM table y 
        WHERE x.id = y.id AND ColA IS NULL) As ColD
FROM table x
WHERE ColA IS NOT NULL
  OR NOT EXISTS (
     SELECT 1 FROM table z
     WHERE  x.id = z.id AND z.ColA IS NOT NULL
  )

SELECT x.ID,x.ColA,x.ColB,
       y.ColC,y.ColD
FROM table x
LEFT JOIN table y
ON x.ID = y.ID AND y.ColA IS NULL
WHERE x.ColA IS NOT NULL 
   OR NOT EXISTS (
         SELECT 1 FROM table z
         WHERE  x.id = z.id AND z.ColA IS NOT NULL
      )

Upvotes: 1

Robert Kock
Robert Kock

Reputation: 6038

You could try this:

SELECT filled.ID,
       filled.ColA,
       filled.ColB AS ColB_1,
       empty.ColB  AS ColB_2,
       empty.ColC,
       empty.ColD
FROM   my_table filled
JOIN   my_table empty
  ON   filled.ID = empty.ID
 AND   empty.ColA IS NULL
WHERE  filled.ColA IS NOT NULL;

Use a LEFT OUTER JOIN in case the row with ColA IS NULL may not exist.

Upvotes: 0

Related Questions