Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jul 6th, 2012  |  syntax: None  |  size: 1.87 KB  |  hits: 17  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Addresses stored in SQL server have many small variations(errors)
  2. CompanyCode   CompanyName                     Addr1                City         State   Zip
  3. 10033         UNITED DIE  CUTTING & FINISHIN  3610 HAMILTON AVE    CLEVELAND    Ohio    44114
  4. 10033         UNITED DIE CUTTING & FINISHING  3610 HAMILTON AVE    CLEVELAND    Ohio    44114
  5. 10033         UNITED DIE CUTTING & FINISHING  3610 HAMILTON AVE.   CLEVELAND    Ohio    44114
  6. 10033         UNITED DIE CUTTING & FINISHING  3610 HAMILTON AVENUE CLEVELAND    Ohio    44114
  7. 10033         UNITED DIECUTTING & FINISHING   3610 HAMILTON AVE    CLEVELAND    Ohio    44144
  8. 10033         UNITED FINISHING                3610 HAMILTON AVE    CLEVLAND     Ohio    44114
  9. 10033         UNITED FINISHING & DIE CUTTING  3610 HAMILTON AVE    CLEVELAND    Ohio    44114
  10.        
  11. CompanyCode   CompanyName                     Addr1                City         State   Zip
  12.  10033         UNITED DIE CUTTING & FINISHING  3610 HAMILTON AVE    CLEVELAND    Ohio    44114
  13.        
  14. select CompanyCode,
  15.     (select top 1 CompanyName from Table1 where CompanyCode=X.CompanyCode
  16.      group by CompanyName order by count(*) desc) CompanyName,
  17.     (select top 1 Addr1 from Table1 where CompanyCode=X.CompanyCode
  18.      group by Addr1 order by count(*) desc) Addr1,
  19.     (select top 1 City from Table1 where CompanyCode=X.CompanyCode
  20.      group by City order by count(*) desc) City,
  21.     (select top 1 State from Table1 where CompanyCode=X.CompanyCode
  22.      group by State order by count(*) desc) State,
  23.     (select top 1 Zip from Table1 where CompanyCode=X.CompanyCode
  24.      group by Zip order by count(*) desc) Zip
  25. from    Table1 X
  26. group by CompanyCode
  27.        
  28. select C1.* from Company C1,
  29. (select CompanyCode, min(CompanyName) as CompanyNameSelected
  30.    from Company
  31.    group by CompanyCode) C2
  32. where
  33.    C1.CompanyCode = C2.CompanyCode and
  34.    C1.CompanyName = C2.CompanyNameSelected;
create a new version of this paste RAW Paste Data