StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIdentifying duplicate GROUPS of data in SQL
text
Body
copied!<p>My question is how to identify duplicate (repeating) 'groups' of data within an SQL table. I am using SQL Server 2005 at the moment so prefer solutions based on that or ansi-sql.</p> <p>Here is a sample table and expected result (below) to base this question on:</p> <pre><code>declare @data table (id nvarchar(10), fund nvarchar(1), xtype nvarchar(1)) insert into @data select 'Switch_1', 'A', 'S' insert into @data select 'Switch_1', 'X', 'B' insert into @data select 'Switch_1', 'Y', 'B' insert into @data select 'Switch_1', 'Z', 'B' insert into @data select 'Switch_2', 'A', 'S' insert into @data select 'Switch_2', 'X', 'B' insert into @data select 'Switch_2', 'Y', 'B' insert into @data select 'Switch_2', 'Z', 'B' insert into @data select 'Switch_3', 'C', 'S' insert into @data select 'Switch_3', 'D', 'B' insert into @data select 'Switch_4', 'C', 'S' insert into @data select 'Switch_4', 'F', 'B' </code></pre> <p>(new data)</p> <pre><code>insert into @data select 'Switch_5', 'A', 'S' insert into @data select 'Switch_5', 'X', 'B' insert into @data select 'Switch_5', 'Y', 'B' insert into @data select 'Switch_5', 'Z', 'B' </code></pre> <hr> <pre><code>-- id fund xtype match -- ---------- ---- ----- --------- -- Switch_1 A S Match_1 -- Switch_1 X B Match_1 -- Switch_1 Y B Match_1 -- Switch_1 Z B Match_1 -- Switch_2 A S Match_1 -- Switch_2 X B Match_1 -- Switch_2 Y B Match_1 -- Switch_2 Z B Match_1 -- Switch_3 C S -- Switch_3 D B -- Switch_4 C S -- Switch_4 F B </code></pre> <p>(new results)</p> <pre><code>-- Switch_5 A S Match_1 -- Switch_5 X B Match_1 -- Switch_5 Y B Match_1 -- Switch_5 Z B Match_1 </code></pre> <p>I only want matches on an ALL or NOTHING basis (i.e. All records in the group match all records in another group - not a part match). Any match id can be used (I have used Match_1 above but can be numeric etc.)</p> <p>Thanks for any help here. </p> <p>(EDIT: I guess I should add that there could be any number of rows per group, not just the 2 or 4 shown in the sample above - and I'm also trying to avoid cursors)</p> <p>(EDIT 2: I seem to have an issue if there are more than one matches found. The output from the SQL supplied is returning duplicate records for Switch_1 when there are more than one matches found. I have updated the sample data accordingly. Not sure if Lieven is still following this - I'm also looking at the solution and will post here if found.)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload