Hi,

Is there a way to 'merge' almost duplicate records. I have records which
      are the same but the other duplicate record can differ from the
other. Normally i just delete the duplicate records, but in this case i
can't.

For example (in a 1-N relation):

id    |    field1    |    field2    |    field3
1    |    11.5    |    52.0    |    null
1    |    null    |    null    |    yes

should be:

id    |    field1    |    field2    |    field3
1    |    11.5    |    52.0    |    yes

Is this possible?

Maybe like this:

SELECT id, SUM(f1), SUM(f2), MAX(f3)
FROM YourTable
GROUP BY id ;

David Portas wrote:
I tried that, but that will only work with numeric values. I have
numeric as well as varchars.

Any idea how i can solve this?

SUM will only work with numerics. MIN and MAX will work with VARCHARs
too.

The answer all depends on what you want to achieve. In you example data
what result would you have required if Field3 on the first row had been
"No" instead of NULL?. You would have to devise an expression that
makes some sense based on your knowledge of the data.

There may be other alternatives. For example you could pick the row for
each ID that has the fewest NULL attributes. Example:

ALTER TABLE YourTable ADD i INTEGER IDENTITY
GO

SELECT P.id, P.f1, P.f2, P.f3
FROM YourTable AS P,
(SELECT MIN(i) AS i
  FROM YourTable AS T2
  WHERE
    CASE WHEN T2.f1 IS NULL THEN 1 ELSE 0 END+
    CASE WHEN T2.f2 IS NULL THEN 1 ELSE 0 END+
    CASE WHEN T2.f3 IS NULL THEN 1 ELSE 0 END =
   (SELECT MIN(
    CASE WHEN T3.f1 IS NULL THEN 1 ELSE 0 END+
    CASE WHEN T3.f2 IS NULL THEN 1 ELSE 0 END+
    CASE WHEN T3.f3 IS NULL THEN 1 ELSE 0 END)
    FROM YourTable AS T3
    WHERE T2.id = T3.id)
  GROUP BY id) AS Q
WHERE P.i = Q.i ;

Torn over a normalizing decision
Mulitple Create Views in Query Batch
Strange Timeout problem.
Book Recommendations
Question about functions