Deduplicating value pairs between 2 columns

Question

This was asked in the SQL Helpline room:

I have a sql query concern. Could you help me?
It goes like this. I have a table with the Records

A B
A C
B A
B C
C A
C B

How do I get only the records that there aren't duplicated in both columns

A B
A C
B C

While it seemed simple at first, it was surprisingly difficult to get it to work. In the end, I had to resort to a loop. Please mind, this looks like example code, but it is more like practice code, so that the logic can be applied to real situations.

I saved a demo here (which uses temp tables) and the query goes like this:

/* Assuming that @Origin is the source table your data currently is: */
declare @Origin table (val1 char(1), val2 char(1));
insert into @Origin (val1, val2) 
values
  ('A', 'B'),
  ('A', 'C'),
  ('B', 'A'),
  ('B', 'C'),
  ('C', 'A'),
  ('C', 'B');

/* Create a temp table in memory:
    Note: We could also use a physicial #holdingTable 
    if the result set is too large for memory */
declare @holdingTable table (
  rownum int primary key, 
  val1 char(1), 
  val2 char(1)
);
insert into @holdingTable (
    rownum, 
    val1, 
    val2
)
select 
  /* generate a rownum to use for looping: */
  row_number() over (order by val1, val2),
  /* add values from origin table: */
  val1,
  val2
from @Origin;

/* loop variables: */
declare @i int = (select min(rownum) from @holdingTable);
declare @max int = (select max(rownum) from @holdingTable);

/* deduplication variables: */
declare @val1Deduplicator char(1);
declare @val2Deduplicator char(1);

/* table to hold result set until end of query: */
declare @resultSet table (val1 char(1), val2 char(1));

while @i <= @max
begin
    /* assign values to deduplicate across columns: */
    select 
        /* inversion between val1 and val2 happens here: */
        @val1Deduplicator = val2, 
        @val2Deduplicator = val1
    from @holdingTable
    where rownum = @i;
    /* verify we are not inserting a duplicate: */
    if not exists (
        select 1 
        from @resultSet 
        where val1 = @val1Deduplicator
          and val2 = @val2Deduplicator
    )
    /* if confirmed not a duplicate, insert the values from the current row: */
    begin
        insert into @resultSet (val1, val2)
        select val1, val2 
        from @holdingTable
        where rownum = @i;
    end;
    /* increment loop: */
    set @i = @i + 1;
end;
/* obtain the final results: */
select * from @resultSet;

The performance seems OK for this few records, but it's not set-based and might get really slow for a large number of records. Is there a smarter way to do this? Is there anything else I can improve?

Output is correct:

val1 val2 
---- ---- 
A    B    
A    C    
B    C    

(3 row(s) returned)

Answer 1

If val1 and val2 are comparable it is easy to find the distinct values, using set-based operations, by normalizing first and then taking distinct rows.

Given:

CREATE TABLE [TestTable]
(
    [TestID] INT NOT NULL IDENTITY(1,1),
    [Value1] NCHAR(1) NOT NULL,
    [Value2] NCHAR(1) NOT NULL,
)

INSERT INTO [TestTable]
    ([Value1], [Value2])
VALUES
    (N'A', N'B'),
    (N'A', N'C'),
    (N'B', N'A'),
    (N'B', N'C'),
    (N'C', N'A'),
    (N'C', N'B'),
    (N'D', N'C');

If you just need the values, and not the actual rows, you can use SELECT DISTINCT with a normalized CTE:

WITH [Normalized] AS
(
    SELECT
        CASE WHEN [tt].[Value1] > [tt].[Value2] 
            THEN [tt].[Value2] 
            ELSE [tt].[Value1] END [N1],
        CASE WHEN [tt].[Value1] > [tt].[Value2] 
            THEN [tt].[Value1] 
            ELSE [tt].[Value2] END [N2]
    FROM
        [TestTable] [tt]
)
SELECT DISTINCT
    [n].[N1] [Value1],
    [n].[N2] [Value2]
FROM
    [Normalized] [n];

Result:

Value1 Value2
------ ------
A      B
A      C
B      C
C      D

If you need the original rows instead you have to eliminate the duplicates yourself. This can be done using the normalized CTE and partitioning by the normalized values:

WITH [Normalized] AS
(
    SELECT
        CASE WHEN [tt].[Value1] > [tt].[Value2] 
            THEN [tt].[Value2] 
            ELSE [tt].[Value1] END [N1],
        CASE WHEN [tt].[Value1] > [tt].[Value2] 
            THEN [tt].[Value1] 
            ELSE [tt].[Value2] END [N2],
        [tt].[TestID],
        [tt].[Value1],
        [tt].[Value2]
    FROM
        [TestTable] [tt]
),
[Ranked] AS
(
    SELECT
        [n].[TestID],
        [n].[Value1],
        [n].[Value2],
        ROW_NUMBER() OVER (PARTITION BY [n].[N1], [n].[N2]
                           ORDER BY [n].[TestID] ASC) AS [Rank]
    FROM
        [Normalized] [n]
),
[Distinct] AS
(
    SELECT
        [r].[TestID],
        [r].[Value1],
        [r].[Value2]
    FROM
        [Ranked] [r]
    WHERE
        [r].[Rank] = 1
)
SELECT [d].[TestID], [d].[Value1], [d].[Value2]
FROM
    [Distinct] [d];

Result:

TestID      Value1 Value2
----------- ------ ------
1           A      B
2           A      C
4           B      C
7           D      C

As for performance, the iterative approach is, according to the execution plan in SSMS14, about 10 times slower. The set based solution is a table scan + a sort where as the execution plan for the iterative solution is a mess to look at.

Answer 2

You just need the correct logic.

This returns one of pairs, prefering A-B over B-A:

SELECT val1,val2 -- need to add DISTINCT if there are duplicate rows
FROM @Origin t1
WHERE NOT EXISTS(
  SELECT * FROM @Origin t2
  WHERE t1.val1 = t2.val2
    AND t1.val2 = t2.val1
    AND t1.val1 > t2.val1
);