从不同的列中删除重复项

这可能不是正确答案，但这适用于您提供给我们的数据。

;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

这是一种相当复杂的方法，我相信可能有更好的方法，但它可以适应处理更多的列。

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;

本质上，它对列中的数据进行排序，然后选择不同的行。

我使用了由Aaron Bertrand修改的Jeff Moden split string 函数，并进一步修改它以按排序顺序返回所有列的连接字符串。我将函数与表交叉应用，并使用返回的列值删除所有重复项。以下是我的实现。

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;