1

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.

  • Does the resultset need to look exactly like that? Could row 1 be 'DEF','ABC' instead? If it needs to be ABC first is that based on alphabetical, or some sorting column you haven't shown us? – Aaron Bertrand Jan 13 '15 at 16:41
  • The values are arbitrary, they could be anything and as long as we only see each pair once they can be any order. Thanks! – Ed Elliott Jan 13 '15 at 16:48
  • And if you have a third row ABC, HJK what happens then? I think you need to supply wider sample data with predictable edge cases to solve this exactly once. – Aaron Bertrand Jan 13 '15 at 17:09
4

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
  • I've edited my answer. This actually seems to work. I've tried removing ABC DEF, DEF ABC combinations, tried changing greater sign into less sign. Result looked right. Perhaps I'm missing something again. Slap me if I am. – Evaldas Buinauskas Jan 13 '15 at 19:37
  • Hey, this works great and is exactly it! Thanks. – Ed Elliott Jan 13 '15 at 21:35
0

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.

0

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;
  • Fancy SQL but why is splitting strings needed in this question? – ypercubeᵀᴹ Jan 13 '15 at 21:42
  • Just an attempt to generalize the solution for n number of columns. I bet there can be a better way. – user353gre3 Jan 13 '15 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.