I want to eliminate any row in which the value of Column A appears in Column B along with the value of Column B appearing in Column A.

For example

``id | column_A | column_B------------------------1 | quick | brown2 | quick | fox3 | brown | quick4 | lazy | dog5 | fox | quick``

I am trying to get a result set

``id | column_A | column_B------------------------1 | quick | brown2 | quick | fox4 | lazy | dog``

As you can see the rows with ids 3 & 5 are eliminate as, in the row with id = 3 the value of column_a = brown and column_b = quick which matches the transposed values of id = 1 where column_a = quick and column_b = brown. Similarly with the row with id = 2 eliminating the row where id = 5.

``````DECLARE @Tx TABLE (
ID         INT IDENTITY
,column_A   NVARCHAR(20)
,column_B   NVARCHAR(20)
)

INSERT INTO @Tx VALUES
('quick','brown')
,('quick','fox')
,('brown','quick')
,('lazy','dog')
,('fox','quick')

;WITH   RN
AS (
SELECT ID,
CASE WHEN column_A < column_B THEN column_A + column_B
ELSE column_B + column_A END AS RNx
FROM @Tx
),

RO
AS (
SELECT ID, RNx, ROW_NUMBER() OVER (PARTITION BY RNx ORDER BY ID) AS RON
FROM RN
)

DELETE Tx
FROM @Tx Tx
LEFT JOIN RO
ON Tx.ID = RO.ID AND RO.RON > 1
WHERE RO.ID IS NOT NULL

SELECT * FROM @Tx
``````

Top