I have a table that gets results from three different sources. Each column represents a source, and each row a result of an outcome. There are over 50k rows for a total of 150k results.

I need to run a report that within these results, I want to remove duplicates leaving the unique values behind, in their respective columns. The majority of the results will all be duplicates, and I would assume around ~500 are unique.

The other 'remove duplicate from multiple columns' posts haven't worked for me; any combo of distinct, groups, and unions I have not been able to get to work.

Example of data below. Thanks.

Raw Data: Data'r

Expected Results: Results

Squiggles: Squiggles

  • 2
    SELECT DISTINCT * doesn't give you the results you want? Or are you classifying a duplicate as if any column equals any other column for a given row then you want it removed? – scsimon Nov 1 '18 at 19:38
  • 1
    Expected results would help a lot here. – scsimon Nov 1 '18 at 20:45
  • @scsimon I'm probably just not writing the query properly. Updated the OP with expected results. Thanks. – CGCIC Nov 2 '18 at 11:50
  • how did you get those expected results? Why only 1 value for column1? seems like something for the application layer – scsimon Nov 2 '18 at 15:00
  • 1
    8DC2 in colukmn 2 is also in column 1 so why did it get listed in c2? – scsimon Nov 2 '18 at 19:36

What is the expected outcome? Maybe use a sample table to demonstrate?

column1 | column 2 | column 3
 value1 | value3   | value2
 value2 | value1   | value3
 value3 | value2   | value1

Many assumptions

  • Can values be duplicated across columns? I saw some.
  • What to do with duplicates? Empty the column? Empty the row?
  • I assume you basically want UNIQUE values for each column for the final result?


  • Apologies for the lack of detail, I have added an expected results example. Duplicates across any combinations of columns must be removed. End result desire is unique values left behind in their original column.Thanks. – CGCIC Nov 2 '18 at 11:55
  • DISTINCT is redundant, as UNION already removes duplicated rows – Pere Joan Martorell May 8 at 9:46

I broke this down using pivot and not exists. I really would handle this in the presentation layer though.

--load test data
declare @table table (c1 int, c2 int, c3 int)
insert into @table

--get our unique values in a cte to pivot later
;with cte as(
    --here we add a RN so that we can use pivot without losing values
    r = row_number() over (partition by Col order by (select 1))
    --for each column, we get the unique values where they don't exist in the other two columns
    --we union them together, but give them 1 /2 / 3 column identifier
        1 as Col, c1.c1 as val
        (select distinct t1.c1 from @table t1
         where  not exists (select 1 from @table t2 where t2.c2 = t1.c1)
            and not exists (select 1 from @table t3 where t3.c3 = t1.c1)) c1
        2 as col, c2.c2
        (select distinct t1.c2 from @table t1
         where  not exists (select 1 from @table t2 where t2.c1 = t1.c2)
            and not exists (select 1 from @table t3 where t3.c3 = t1.c2)) c2 
        3 as col, c3.c3
        (select distinct t1.c3 from @table t1
         where  not exists (select 1 from @table t2 where t2.c1 = t1.c3)
            and not exists (select 1 from @table t3 where t3.c2 = t1.c3)) c3
    ) i

--simple pivot
    [1], [2], [3]
from cte 
(max(Val) for Col in ([1],[2],[3]))


|  1   |  2   | 3  |
| 14   | 3    |  6 |
| NULL | 16   |  8 |
| NULL | NULL | 13 |
| NULL | NULL | 15 |
  • Thanks.. that's definitely something I couldn't scheme up. I don't understand the first section though where you load test data? Are you alluding that I will need to list all 150k possible results there somehow? Also, just running the script as is, I get "Invalid column name 'Val'. for the second to last line. – CGCIC Nov 5 '18 at 13:54
  • No, you would ignore that part of the script and replace the column names and table names with your own. I created some test data so you could run the script and see the results.... I'm not sure why you get that error... you can see the script here: dbfiddle.uk/… – scsimon Nov 5 '18 at 14:05
  • Sorry - Do I change the first section to "declare @table table (c1 int, c2 int, c3 int) select 1, 2, 3 from dbo.nums" ? – CGCIC Nov 7 '18 at 17:38
  • You need to replace @table with your actual table name, and all of the column names with your column names. I don't understand how this is confusing – scsimon Nov 7 '18 at 17:41
  • I use SQL a few times a year at best, so my common logic doesn't always click. I updated the original question with a picture. The dbo i'm using is NUMs, and the actual columns are 1,2,3. Is that causing an issue? – CGCIC Nov 8 '18 at 19:17

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.