Group By & Eliminate varying Values

Scenario 1:

Table:

id column1 column2
1  "bla"   "foo"
2  "bla"   "bar"

I want to group by column1 and get null fo开发者_运维问答r column2, cause there's not the same value in all rows.

Scenario 2:

Table:

id column1 column2
1  "bla"   "foo"
2  "bla"   "foo"

I want to group by column1 and get foo for column2, cause all values of column2 are equal.

Is it possible to solve this by a sql statement?

Since you want to group by column1, one way to know if column2 has all same values is to check if max(column2) = min(column2), therefore this should work:

select column1, case 
                    when max(column2) = min(column2) then max(column2)
                    else null
                end as col2
  from tabletest
 group by column1;

Edit

If your column2 cannot accept null values, then the above query is ok, otherwise you need the following one to handle cases when column2 is null:

select t.column1, case 
                      when (select max(1) from tabletest where column2 is null and column1 = t.column1) = 1 then null
                      when max(t.column2) = min(t.column2) then max(t.column2)
                      else null
                  end as col2
  from tabletest t
 group by t.column1;

The only difference is that we need to add a case condition to cover the case of when column2 is null

Try this:

select id = t.id ,
       col1 = t.col1 ,
       col2 = case when t1.N < 2 then t.col2 end
from myTable t
join ( select col1,N=count(distinct col2)
       from myTable
       group by col1
     ) t1 on t1.col1 = t.col1