469,167 Members | 1,256 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Merge results from two fields

What is the best way to go about merging the results of two fields in
the same table?

I have two different fields that hold lists of names, some of them
identical, some different. From those I'd like to generate a merged
list with all the distinct names.

For example:

Field1 contains: Jack, Joe, Jim, Paul
Field2 contains: Peter, Paul, Joe, Jim

The result should be: Jack, Joe, Jim, Peter, Paul

Jul 23 '05 #1
5 3976
Why are you storing delimited lists in columns? This is a bad idea in a
relational database. Best to create a new table and put the data in
there. SELECT DISTINCT will give you a distinct list from a table but
it's tricky to manipulate delimited lists in SQL.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2
I'm sorry, I meant to convey that these names are different records in
the particular fields.

So - I may have the following records:

<pre>
Rec# Field1 Field2
1: Jack Peter
2: Joe Paul
3: Jim Joe
4: Paul Jim
</pre>

and would like a result of:
Jack
Joe
Jim
Paul
Peter

Jul 23 '05 #3
Create table statements and insert statements for sample data will
prevent these kinds of misunderstandings. In the future, please try to
include those in your posts.

Off the top of my head (meaning it might not be the best solution
performance-wise), the following should work:

CREATE TABLE My_Table (field1 VARCHAR(20), field2 VARCHAR(20))
GO

INSERT INTO My_Table VALUES ('Jack', 'Peter')
INSERT INTO My_Table VALUES ('Joe', 'Paul')
INSERT INTO My_Table VALUES ('Jim', 'Joe')
INSERT INTO My_Table VALUES ('Paul', 'Jim')
GO

SELECT Field1 AS Name FROM My_Table
UNION
SELECT Field2 FROM My_Table
GO

HTH,
-Tom.

Jul 23 '05 #4
Taw
This should do it

select distinct Field1 from TableName
union
select distinct Field2 from TableName
Stacey wrote:
I'm sorry, I meant to convey that these names are different records in the particular fields.

So - I may have the following records:

<pre>
Rec# Field1 Field2
1: Jack Peter
2: Joe Paul
3: Jim Joe
4: Paul Jim
</pre>

and would like a result of:
Jack
Joe
Jim
Paul
Peter


Jul 23 '05 #5
Do not use SELECT DISTINCT and UNION together. It is redundant and
most SQL products will not see that, so you get three sorts instead of
one.

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Tom Keane | last post: by
2 posts views Thread by Aaron | last post: by
8 posts views Thread by Squirrel | last post: by
reply views Thread by beerman | last post: by
8 posts views Thread by Darryl Kerkeslager | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.