How to store table with a large number of fields?

In SQL Server, how can I look for dupes on multiple similar fields?

• My apologies up front if this doesn't make sense. My table has 5 phone number fields. Most of the records have the same value in 2 or 3 of these fields. I want to list each unique phone number from *any* of these fields, but only if it exists in >1 record in any of these 5 fields, and I need the record count associated with each of these phone numbers. In other words I want to dupe check each phone# field against each of the other phone# fields, excluding those matches that come from within the same record. I am happy to answer clarifying questions if anyone is willing to help with this.

• Answer:

  I would first merge the fields into a single virtual column. Then you use a basic "find dupes" query using COUNT and HAVING on the GROUP. Depending on the volume of records and activity you have, you may want to use temp tables or work tables. Here is an example with three fields, so if this is a homework question, you still have a bit more work to do, but that should give you a technique you can elaborate on. You can include the RowId (replace with your key) in your grouping to isolate "dupes per record". 1 2 3 4 5 6 7 8 91011121314151617181920212223242526272829303132333435363738394041CREATE TABLE [dbo].[test]( [RowId] [int] IDENTITY(1,1) NOT NULL, [P1] [varchar](50) NULL, [P2] [varchar](50) NULL, [P3] [varchar](50) NULL, ) ---------------------------------------------------------------------------------------------------- RowId       P1                                                 P2                                                 P3 ----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 1           5554448899                                         5554448899                                         NULL 2           5554447777                                         5554442222                                         5554441111 3           5553330000                                         5553339999                                         5553330000 4           4442226666                                         NULL                                               NULL 5           4442226666                                         4442225555                                         NULL (5 row(s) affected) ---------------------------------------------------------------------------------------------------- SELECT X.[Phone], COUNT(X.[Phone]) AS Count FROM (   SELECT [RowId], [P1] AS Phone FROM [test] WHERE [P1] IS NOT NULL   UNION ALL   SELECT [RowId], [P2] AS Phone FROM [test]  WHERE [P2] IS NOT NULL   UNION ALL   SELECT [RowId], [P3] AS Phone FROM [test]  WHERE [P3] IS NOT NULL ) X GROUP BY X.[Phone] HAVING ( COUNT(X.[Phone]) > 1 ) ---------------------------------------------------------------------------------------------------- Phone                                              Count -------------------------------------------------- ----------- 4442226666                                         2 5553330000                                         2 5554448899                                         2 (3 row(s) affected)