CODEWITHSUNDEEP
sqlunique
abc1234abc
abc1234abc

Reputation: 11

Merging two columns but only unique combinations

I have two columns, each with identification numbers that have been brought in from different datasheets.

I want to combine this into one column with both identification numbers if they are different, but only one of the identification numbers if they are the same.

I'm using SELECT DISTINCT CONCAT(column 1, column 2) AS column 3 to combine the columns, but can not filter out UNIQUE combinations.

When I try WHERE column 1 <> column 2, I get an error message.

Any suggestions?

Upvotes: 1

Views: 99

Answers (3)

JBrooks
JBrooks

Reputation: 10013

You should do something like:

SELECT DISTINCT CASE WHEN column1 = column2 THEN column1
                     ELSE column1 + '|' + column2
                     END AS combinedColumn
FROM table1

Consider the following chart:

column1  column2  column1+column2   column1+'|'+column2
12       34          1234                 12|34
123      4           1234                 123|4
1234     1234        1234                 1234

Also, column1+column2 loses some information - what the original parts were.

Upvotes: 0

Racil Hilan
Racil Hilan

Reputation: 25341

You can use CASE WHEN to test for conditions:

SELECT DISTINCT CASE WHEN column1 = column2 THEN column1
                     ELSE CONCAT(column1, column2)
                     END AS column3
FROM table1

Upvotes: 1

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

try this using IIF or CASE and CONCAT

select
distinct
iif(col1<>col2,concat(col1,col2),col1) [myid]
from mytable

or 

select
distinct
case when col1<>col2 then
     concat(col1,col2)
else col1 end [myid]
from mytable

Upvotes: 0

Related Questions