Merge two columns results in one column - SQL

Question

Sample data -

CREATE TABLE dbo.#test
(
    id int NOT NULL,
    name varchar (10) NULL,
    name2 varchar (10) null
);

insert into #test values ('1','abc','abc')
insert into #test values ('1','abc','yyy')
insert into #test values ('1','abc','zzz')
insert into #test values ('1','abc','ddd')

select * from #test

Now, I'm trying to join/merge column 'name' and 'name2' followed by remove duplicates and shows value as below - Any thoughts ?

Name
abc
ddd
yyy
zzz

I need to get this done using CASE statement i.e., sample code is below. (Albeit this can be achieved by using UNION but I need to use CASE Statement)

select  case 'b'
when 'a'
then name 
when 'b'
then coalesce (name , name2 )
end as NAME from #test

Why do you need to use a CASE? UNION is the best option here... — Siyual
– Siyual, Commented Dec 17, 2015 at 21:38
I really don't get why you'd use CASE to do this. Really, really odd. — Mat Richardson
– Mat Richardson, Commented Dec 17, 2015 at 21:41

Mat Richardson · Accepted Answer · 2015-12-17 21:50:20Z

2

This is awful and should really be done using a UNION, but I think it's what you were after in this example:-

select
  case
    when (select count(*) from #test b where b.name = a.name2) > 1 then a.name
    else a.name2
  end as Name
from #test a

Really though, you should have something like this:-

select name from #test
union
select name2 from #test

answered Dec 17, 2015 at 21:50

Mat Richardson

3,5985 gold badges37 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Krishnraj Rana · Accepted Answer · 2015-12-17 21:51:09Z

0

Well one of the way to get desired result is by using Union

and another alternate way is this -

SELECT id
    ,(
        SELECT max(name1)
        FROM (
            VALUES (name)
                ,(name2)
            ) res(name1)
        ) AS name
FROM #test

Result

id  Name
--------
 1  abc 
 1  ddd
 1  yyy
 1  zzz

answered Dec 17, 2015 at 21:51

Krishnraj Rana

6,6483 gold badges33 silver badges37 bronze badges

Collectives™ on Stack Overflow

Merge two columns results in one column - SQL

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related