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, PaulWhy 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
--|||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|||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.|||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|||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.

Merge Replication issue .. Please help (Replication does not work)

How is the network relationship set up? If this is a non-
trusted relationship, you'll need to register an alias in
the client network utility on the subscriber, and place
an entry in the hosts file there also.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul,
Thank you for your reply.
This is a non-trusted relationship.I registered a client network utility
with the alias pointing to my publisher/ distributor and added an entry in
the host file also.
But still I 'm getting this same message in the job history:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
What is wrong am I doing?
Sam
"Paul Ibison" wrote:

> How is the network relationship set up? If this is a non-
> trusted relationship, you'll need to register an alias in
> the client network utility on the subscriber, and place
> an entry in the hosts file there also.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Also I tried to push the subscription instead of pullin git from subscriber.
But in push subscription , the merge agent throws this error:
The remote server is not defined as a subscription server.
How do I set up the remote server as subscription server?
The merge agent does transfers lot of data and objects from publisher to
subscriber , it is sometime at the end of the process , it fails.
Please advice..
Sam
"Paul Ibison" wrote:

> How is the network relationship set up? If this is a non-
> trusted relationship, you'll need to register an alias in
> the client network utility on the subscriber, and place
> an entry in the hosts file there also.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Sam,
have you set up FTP for the initialization? Have a look at this article:
http://support.microsoft.com/?id=321822
Also, check that the correct port has been opened up on the firewall (port
1433 for default instance of SQL Server, by default).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)