Combine the columns with null values in SQL

Question

I have SQL query which fetches results like this

Column1 Column2     Column3 Column4    Column5 Column6 Coulmn7
A-66001 46063       137039  0-82-A0-A0 NULL   NULL     NULL
A-66001 46063       139045  NULL       NULL   NULL     NULL
A-66001 46063       141051  NULL       40     NULL     30
A-66001 46063       237164  NULL    NULL      20       NULL

I want a result combining all the rows like this .

Column1 Column2     Column3 Column4    Column5 Column6 Coulmn7
A-66001 46063       137039  0-82-A0-A0 40        20     30

I tried using Max function but that did not work for string . Need the solution at the earliest

What do you mean max() did not work for "string"? What happened? max() works fine on strings. — Gordon Linoff
– Gordon Linoff, Commented Oct 7, 2014 at 10:56

Gordon Linoff · Accepted Answer · 2014-10-07 10:56:32Z

2

Use max():

select column1, column2, min(column3) as column3,
       max(column4) as column4, 
       max(column5) as column5, 
       max(column6) as column6, 
       max(column7) as column7 
from table t
group by column1, column2;

I am guessing that you want to really group by the first two columns and any arbitrary value for Column3 is appropriate.

answered Oct 7, 2014 at 10:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2861354 Over a year ago

i want group by coulmn1 and column2 , coulmn 3 is not required but i want all the null from column4,column5,column6,column7 and display only one column displaying the values in that column

user2861354 Over a year ago

I tried using max(column4) this earlier ,but it did not work for me , but now this is working

Gordon Linoff Over a year ago

min() and max() work on almost all types, including strings (sometimes they are not allowed on big data types such as blobs, but otherwise they are allowed on almost all types).

Ronak Shah · Accepted Answer · 2014-10-07 11:14:58Z

0

Try below:

select
(select column1 from table where column1 is not null limit 1) as column1
(select column2 from table where column2 is not null limit 1) as column2
(select column3 from table where column3 is not null limit 1) as column3
(select column4 from table where column4 is not null limit 1) as column4
(select max(cast(column5 as int)) from table where column5 is not null limit 1) as column5
(select max(cast(column6 as int)) from table where column6 is not null limit 1) as column6
(select max(cast(column7 as int)) from table where column7 is not null limit 1) as column7

from table

edited Oct 7, 2014 at 11:14

answered Oct 7, 2014 at 11:03

Ronak Shah

1,5592 gold badges13 silver badges20 bronze badges

Collectives™ on Stack Overflow

Combine the columns with null values in SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related