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 at 19:38
  • 1
    Expected results would help a lot here. – scsimon Nov 1 at 20:45
  • @scsimon I'm probably just not writing the query properly. Updated the OP with expected results. Thanks. – CGCIC Nov 2 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 at 15:00
  • 1
    8DC2 in colukmn 2 is also in column 1 so why did it get listed in c2? – scsimon Nov 2 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?

Try:

SELECT DISTINCT column1 FROM tableA 
UNION 
SELECT DISTINCT column2 FROM tableA 
UNION
SELECT DISTINCT column3 FROM tableA
  • 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 at 11:55

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
values
(1,1,1)
,(1,1,1)
,(2,3,2)
,(4,2,4)
,(5,4,6)
,(7,5,8)
,(9,7,11)
,(11,9,13)
,(14,16,15)

--get our unique values in a cte to pivot later
;with cte as(
select 
    --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))
    ,i.*
from
    (
    --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
    select
        1 as Col, c1.c1 as val
    from
        (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
    union
    select 
        2 as col, c2.c2
    from
        (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 
    union
    select
        3 as col, c3.c3
    from
        (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
select
    [1], [2], [3]
from cte 
pivot
(max(Val) for Col in ([1],[2],[3]))
p

RETURNS

+------+------+----+
|  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 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 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 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 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 at 19:17

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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