CODEWITHSUNDEEP
sqloracle-database
Justin
Justin

Reputation: 23

Merge multiple rows into 1 in oracle sql

My search-fu has failed me, but I THINK i'm on the right track with the MERGE statement.

I have a table that looks like this:

P_K,Col_1,Col2,Col3
1,word,null,null
1,null,number,null
1,null,null,3

I want the result of my statement to look like this:

P_K,Col_1,Col_2,Col_3
1,word,number,3

Thank you!

Upvotes: 0

Views: 69

Answers (2)

Be Kind To New Users
Be Kind To New Users

Reputation: 10063

You are short on a few details to really give you a good answer, but here is one way to do it with the very small set of data you have provided.

select x.P_K
      ,(select a.col_1 from t a where a.p_k = x.p_k and a.col_1 not NULL) as col_1
      ,(select b.col_2 from t b where b.p_k = x.p_k and b.col_2 not NULL) as col_2
      ,(select c.col_3 from t c where c.p_k = x.p_k and c.col_3 not NULL) as col_3
  from t x
 group by x.P_K

Another way might be like this:

select x.P_K
      ,MAX(x.col_1) as col_1
      ,MAX(x.col_2) as col_2
      ,MAX(x.col_3) as col_3
  from t x
 group by x.P_K

The one you use will depend on the edge cases in your data.

If you want to tolerate more than one row for each of the col_N, then you use MAX. If you want the select to fail if there is more than one row for each of the col_N, then use the subselect technique.

By fail I mean the database engine will report an error that says more than one row returned in a context where only one row is allowed. Depending on what you are doing, you may want to know about data that does not fit the pattern.

The MAX() technique will run faster.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231651

If you just want a query that returns one row of data, simple aggregation should work

SELECT t.p_k,
       max( t.col_1 ) col_1,
       max( t.col_2 ) col_2,
       max( t.col_3 ) col_3
  FROM table_name t
 GROUP BY t.p_k

Upvotes: 1

Related Questions