Remove duplicates from different columns

Question

I'm trying to remove duplicates from a set, but the duplicates are in different columns, so for example with this table:

ColA, ColB, ColC, ColD
----------
1,  1, 'ABC', 'DEF'
----------
1,  1, 'DEF', 'ABC'
----------
1,  1, 'GHJ', 'LKJ'
----------
1,  1, 'LKJ', 'GHJ'

What I need to end up with is:

ColA, ColB, ColC, ColD
----------
1,  1, 'ABC', 'DEF'
1,  1, 'GHJ', 'LKJ'

Hope that makes sense, does anyone have any ideas?

This is SQL-Server 2012.

Answer 1

This probably ain't going to be right answer, but this works for data you gave us.

;WITH TestData (ColA, ColB, ColC, ColD)
AS (
    SELECT 1, 1, 'ABC', 'DEF'
    UNION ALL
    SELECT 1, 1, 'DEF', 'ABC'
    UNION ALL
    SELECT 1, 1, 'GHJ', 'LKJ'
    UNION ALL
    SELECT 1, 1, 'LKJ', 'GHJ'
    UNION ALL
    SELECT 1, 1, 'ABC', 'HJK'
    UNION ALL
    SELECT 1, 1, 'HJK', 'ABC'
)
SELECT ColA, ColB, ColC, ColD
FROM TestData
EXCEPT
SELECT ColA, ColB, ColD, ColC
FROM TestData
WHERE ColC < ColD

Answer 2

This is a pretty convoluted way to do it and I am sure there is likely to better ways, but it can be adapted to handle more columns.

SELECT DISTINCT 
    ColA
    , ColB
    , ColC
    , ColD
FROM (
    SELECT ColA
        , ColB
        , MAX(CASE WHEN S = 1 THEN ColCD END) ColC
        , MAX(CASE WHEN S = 2 THEN ColCD END) ColD
    FROM (
        SELECT ColA
            , ColB
            , ColCD
            , ROW_NUMBER() OVER (PARTITION BY N ORDER BY ColCD) S
            , N
        FROM (
            SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
                , * 
            FROM TestData
            ) t 
            CROSS APPLY (VALUES(ColC),(ColD)) X (ColCD)
        ) A
    GROUP BY N, ColA, ColB
    ) B;

Essentially it resorts the data in the columns and then selects the distinct rows.

Answer 3

I used Jeff Moden split string function modified by Aaron Bertrand, and modified it further to return concatenated string for all columns in a sorted order. I cross applied the function with the table and used the returned column value remove all the duplicates. Following is my implementation.

Create function dbo.SortStrings_Moden
/*Derived from Jeff Moden's Splitter Function
    http://www.sqlservercentral.com/articles/Tally+Table/72993/
*/
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                         SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (
       SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0)),
       cteSorted as (SELECT Item = SUBSTRING(@List, s.N1, 
        ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
                    From cteStart s)
            Select  substring((
                Select ','+ Item  AS [text()]
                    From cteSorted ORDER BY Item For XML PATH ('') ), 2, 1000) 
        [SortItem];

-- Create sample table #TestData
Create table #TestData (ColA int, ColB int, ColC nvarchar(10), ColD nvarchar(10));

-- Populate the table with sample data
Insert into #TestData
    SELECT 1, 1, 'ABC', 'DEF'
    UNION ALL
    SELECT 1, 1, 'DEF', 'ABC'
    UNION ALL
    SELECT 1, 1, 'GHJ', 'LKJ'
    UNION ALL
    SELECT 1, 1, 'LKJ', 'GHJ'
    UNION ALL
    SELECT 1, 1, 'ABC', 'HJK'
    UNION ALL
    SELECT 1, 1, 'HJK', 'ABC'
    UNION ALL
    SELECT 2, 2, NULL, 'XYZ'
    UNION ALL
    SELECT 2, 2, 'XYZ', NULL;

-- Query to get the results
With cteRN as(
 Select ColA, ColB, ColC, ColD, ROW_NUMBER() over(partition by SortItem 
  order by ColA, ColB, ColC, ColD) as rn from #TestData
 cross apply dbo.SplitStrings_Moden(
     cast(isnull(ColA, '') as varchar(10)) + ',' + 
     cast(isnull(ColB, '') as varchar(10)) + ',' + 
     isnull(ColC, '') + ',' + isnull(ColD, ''), N','))
Select ColA, ColB, ColC, ColD from cteRN where rn = 1;