CODEWITHSUNDEEP
mysqlsql
dionajie
dionajie

Reputation: 404

SQL Server : Group by Replace NULL

i have table like this :

 | id | column_a | column_b |
 |  1 |  NULL    | 111      |
 |  1 |  222     | NULL     |
 |  2 |  NULL    | 333      |
 |  2 |  444     | NULL     |

i want to group it like this 

 | id | column_a | column_b |
 |  1 |  222     | 111      |
 |  2 |  444     | 333      |

Column_a and column_b is varchar. and pattern NULL value is consistent. How can i do that?

Upvotes: 0

Views: 1506

Answers (3)

MtwStark
MtwStark

Reputation: 4048

I think your problem is not to add app numeric values..

maybe you mean something like this..

select ISNULL(ta.id,tb.id) id, ta.a,tb.b
from 
(select * from t where a is not null) ta
full join
(select * from t where b is not null) tb
on ta.id = tb.id

you should explain better the nature of the problem

as @sagi said, what kind of values will receive this table? only two records for each id? one with 1st column null and second column not null and vice versa?

or there are other scenarios?

Upvotes: 0

Avi
Avi

Reputation: 1145

try this one as well...

select id, sum(nvl(column_a,0)) as column_a, 
sum(nvl(column_b,0)) as column_b 
from tbl1
group by id;

Upvotes: 0

Muhammad Abdul Arif Sarker
Muhammad Abdul Arif Sarker

Reputation: 1219

The aggregate function SUM don't handle NULL in the same way as ordinary functions and operators. Instead of returning NULL as soon as a NULL operand is encountered, it only take non-NULL fields into consideration while computing the outcome.

So, you can resolve it this way..

select id, sum(column_a) as column_a, 
sum(column_b) as column_b 
from tbl1
group by id;

Demo Here

Upvotes: 1

Related Questions