CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
SQL_M
SQL_M

Reputation: 2475

Select query for relevant data while removing duplicates

I have a table like this:

ID  Col_A   Col_B   Col_C   Col_D   Col_E   Col_F   Col_G   Col_H
1   yyy     null    NULL    NULL    NULL    NULL    NULL    1
1   yyy     null    NULL    NULL    NULL    NULL    NULL    null
1   yyy     null    NULL    NULL    NULL    NULL    NULL    0
1   yyy     null    NULL    NULL    NULL    NULL    NULL    null
1   yyy     null    NULL    NULL    NULL    NULL    NULL    0
26  null    17,5    NULL    NULL    NULL    NULL    NULL    null
26  null    17,5    NULL    NULL    NULL    NULL    NULL    1
26  null    17,5    NULL    NULL    NULL    NULL    NULL    0
26  null    17,5    NULL    NULL    NULL    NULL    NULL    0
26  null    17,5    NULL    NULL    NULL    NULL    NULL    1

I want to remove duplicate ID's, but keep relevant data. So desired result here would be:

ID  Col_A   Col_B   Col_C   Col_D   Col_E   Col_F   Col_G   Col_H
1   yyy     null    NULL    NULL    NULL    NULL    NULL    1
26  null    17,5    NULL    NULL    NULL    NULL    NULL    1

The code I tried was:

  SELECT ID, 
  MAX (ISNULL (Col_A, 0)) AS Col_A,
  MAX (ISNULL (Col_B, 0)) AS Col_B,
  MAX (ISNULL (Col_C, 0)) AS Col_C,
  MAX (ISNULL (Col_D, 0)) AS Col_D,
  MAX (ISNULL (Col_E, 0)) AS Col_E,
  MAX (ISNULL (Col_F, 0)) AS Col_F,
  MAX (ISNULL (Col_G, 0)) AS Col_G,
  MAX (ISNULL (Col_H, 0)) AS Col_H
  FROM TableA
  GROUP BY ID
  ORDER BY 1

Working on MSSQL 2008

Upvotes: 1

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I would simply do:

select max(col_a) as col_a,
       max(col_b) as col_b,
       . . .
from tablea
group by id
order by id;

The isnull() is unnecessary.

Upvotes: 2

Related Questions