SQL Finding multiple-line duplicates

Question

At my worki we have data stored in a database, the data is not normalized. I am looking for a way to find what data was duplicated.

Our Data base has 3 rows columns, Name, State, Strategy

This data might looks something like this:

OldTable:
Name | State | Strat
-----+-------+------
A    | M     | 1
A    | X     | 3
B    | T     | 6
C    | M     | 1
C    | X     | 3
D    | X     | 3

What I'd like to do is move the data to two tables, one containing the name the other containing the set of State and Strats so it would look more like this

NewTable0:
Name | StratID
-----+--------
A    | 1
B    | 2
C    | 1
D    | 3

NewTable1:
StratID | State | Strat
--------+-------+------
1       | M     | 1    
1       | X     | 3
2       | T     | 6
3       | X     | 3

So in the data example A and C would be duplicates, but D would not be. How would I go about finding and/or identifying these duplicates?

Answer 1

You could find this out by grouping the Names together, and only listing those where there is more than one record:

SELECT OldTable.Name, COUNT(1) Duplicates
FROM OldTable
GROUP BY OldTable.Name
HAVING Duplicates > 1

Should output:

OldTable:
Name | Duplicates
-----+------------
A    | 2
C    | 2

Answer 2

Try:

SELECT OT1.Name Name1, OT2.Name Name2
FROM OldTable OT1
JOIN OldTable OT2 ON OT1.Name < OT2.Name AND 
                     OT1.State = OT2.State AND 
                     OT1.Strat = OT2.Strat
GROUP BY OT1.Name, OT2.Name 
HAVING COUNT(*) = (SELECT COUNT(*) FROM OldTable TC1 WHERE TC1.NAME = OT1.NAME) 
   AND COUNT(*) = (SELECT COUNT(*) FROM OldTable TC2 WHERE TC2.NAME = OT2.NAME)