Combining Duplicate Records In Sql Server

I have a table in SQL Server 2012 that holds a list of parts, location of the parts and the quantity on hand. The problem I have is someone put a space in front of the location whe

Solution 1:

Two steps: 1. update the records with the correct locations, 2. delete the records with the wrong locations.

update mytable
set onhand = onhand + 
(
  selectcoalesce(sum(wrong.onhand), 0)
  from mytable wrong
  where wrong.location like' %'andtrim(wrong.location) = mytable.location
)
where location notlike' %';

deletefrom mytable where location like' %';

Solution 2:

You can do some grouping with a HAVING clause on to identify the records. I've used REPLACE to replace spaces with empty strings in the location column, you could also use LTRIM and RTRIM:

CREATETABLE #Sample
    (
      [Partno] VARCHAR(7) ,
      [PartRev] INT ,
      [Location] VARCHAR(5) ,
      [OnHand] INT ,
      [Identity_Column] INT
    );


INSERTINTO #Sample
    ([Partno], [PartRev], [Location], [OnHand], [Identity_Column])
VALUES
    ('0D6591D', 000, ' MV3', 55.000, 103939),
    ('0D6591D', 000, 'MV3', -55.000, 104618)
;

SELECT Partno ,
       PartRev ,
       REPLACE( Location, ' ', '') Location,
       SUM(OnHand) [OnHand]
FROM #Sample
GROUPBY REPLACE(Location, ' ', '') ,
         Partno ,
         PartRev
HAVINGCOUNT(Identity_Column) >1;

DROPTABLE #Sample;

Produces:

Partno  PartRev Location    OnHand
0D6591D 0       MV3         0