Ygthkb

Identical projects by students at two different colleges: still plagiarism?

Why do single electrical receptacles exist?

What happened to Hermione’s clothing and other possessions after she wiped her parents’ memories of her?

Can I travel from country A to country B to country C without going back to country A?

Is it possible to detect 100% of SQLi with a simple regex?

Players preemptively rolling, even though their rolls are useless or are checking the wrong skills

How can I prevent an oracle who can see into the past from knowing everything that has happened?

Did ancient Germans take pride in leaving the land untouched?

Is "accuse people to be racist" grammatical?

How can changes in personality/values of a person who turned into a vampire be explained?

Is the percentage symbol a constant?

Was there a pre-determined arrangment for division of Germany in case it surrendered before any Soviet forces entered its territory?

Performance and power usage for Raspberry Pi in the Stratosphere

Probability X1 ≥ X2

Tikz: Perpendicular FROM a line

In a post-apocalypse world, with no power and few survivors, would Satnav still work?

How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?

Taking an academic pseudonym?

Homeostasis logic/math problem

How do I fight with Heavy Armor as a Wizard with Tenser's Transformation?

Maybe pigeonhole problem?

What happens to the first ionization potential when a hydrogen-like atom captures a particle?

Why "rm -r" is unable to delete this folder?

Minimum Viable Product for RTS game?

Remove Duplicates (or Select Unique) from multiple rows, with all random data

Remove specific duplicates (all but latest)How do I delete unique rows from a table with duplicate keys?Remove duplicates from different columnsRemove all records with duplicates in db2. (Not just the duplicate records)SELECT rows with duplicate valuesHow to remove duplicates and keep the one with the most additional informationSQLite3 - remove duplicates by multiple columnsClustered Index Help, where did i go wrong?Update all rows from a table with random foreign key from another tableDuplicate records returned from table with no duplicates

0

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:

Expected Results:

Squiggles:

sql-server query duplication

share|improve this question

edited Nov 8 '18 at 19:20

CGCIC

asked Nov 1 '18 at 19:32

CGCICCGCIC

11

bumped to the homepage by Community♦ 13 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  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
  

  
  
  
  

 | 
show 3 more comments

0

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:

Expected Results:

Squiggles:

sql-server query duplication

share|improve this question

edited Nov 8 '18 at 19:20

CGCIC

asked Nov 1 '18 at 19:32

CGCICCGCIC

11

bumped to the homepage by Community♦ 13 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  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
  

  
  
  
  

 | 
show 3 more comments

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:

Expected Results:

Squiggles:

sql-server query duplication

share|improve this question

edited Nov 8 '18 at 19:20

CGCIC

asked Nov 1 '18 at 19:32

CGCICCGCIC

11

share|improve this question

edited Nov 8 '18 at 19:20

CGCIC

asked Nov 1 '18 at 19:32

CGCICCGCIC

11

share|improve this question

edited Nov 8 '18 at 19:20

CGCIC

asked Nov 1 '18 at 19:32

CGCICCGCIC

11

asked Nov 1 '18 at 19:32

CGCICCGCIC

11

asked Nov 1 '18 at 19:32

CGCICCGCIC

11

2 Answers
2

active

oldest

votes

0

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

share|improve this answer

edited Nov 2 '18 at 8:54

Paul White♦

52.2k14279452

answered Nov 2 '18 at 5:52

Jerry HungJerry Hung

1066

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

0

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 |

+------+------+----+

share|improve this answer

answered Nov 2 '18 at 19:54

scsimonscsimon

1,398414

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

 | 
show 2 more comments

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221571%2fremove-duplicates-or-select-unique-from-multiple-rows-with-all-random-data%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

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

share|improve this answer

edited Nov 2 '18 at 8:54

Paul White♦

52.2k14279452

answered Nov 2 '18 at 5:52

Jerry HungJerry Hung

1066

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

0

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

share|improve this answer

edited Nov 2 '18 at 8:54

Paul White♦

52.2k14279452

answered Nov 2 '18 at 5:52

Jerry HungJerry Hung

1066

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

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

share|improve this answer

edited Nov 2 '18 at 8:54

Paul White♦

52.2k14279452

answered Nov 2 '18 at 5:52

Jerry HungJerry Hung

1066

column1 | column 2 | column 3

-----------------------------

 value1 | value3   | value2

 value2 | value1   | value3

 value3 | value2   | value1

SELECT DISTINCT column1 FROM tableA 

UNION 

SELECT DISTINCT column2 FROM tableA 

UNION

SELECT DISTINCT column3 FROM tableA

share|improve this answer

edited Nov 2 '18 at 8:54

Paul White♦

52.2k14279452

answered Nov 2 '18 at 5:52

Jerry HungJerry Hung

1066

edited Nov 2 '18 at 8:54

Paul White♦

52.2k14279452

edited Nov 2 '18 at 8:54

Paul White♦

52.2k14279452

answered Nov 2 '18 at 5:52

Jerry HungJerry Hung

1066

answered Nov 2 '18 at 5:52

Jerry HungJerry Hung

1066

0

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 |

+------+------+----+

share|improve this answer

answered Nov 2 '18 at 19:54

scsimonscsimon

1,398414

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

 | 
show 2 more comments

0

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 |

+------+------+----+

share|improve this answer

answered Nov 2 '18 at 19:54

scsimonscsimon

1,398414

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

 | 
show 2 more comments

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 |

+------+------+----+

share|improve this answer

answered Nov 2 '18 at 19:54

scsimonscsimon

1,398414

--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

+------+------+----+

|  1   |  2   | 3  |

+------+------+----+

| 14   | 3    |  6 |

| NULL | 16   |  8 |

| NULL | NULL | 13 |

| NULL | NULL | 15 |

+------+------+----+

share|improve this answer

answered Nov 2 '18 at 19:54

scsimonscsimon

1,398414

answered Nov 2 '18 at 19:54

scsimonscsimon

1,398414

answered Nov 2 '18 at 19:54

scsimonscsimon

1,398414