merge data help

[Joey Martin]

I have two tables:
customermaster (id,name,email,mailinglists)
example: 1,john doe, jo*****@nowhere.com, NULL
emailmaster (email,listid)
example:
jo*****@nowhere.com 1
jo*****@nowhere.com 8
I want to get rid of the emailmaster table and merge the listids, into
the mailinglists field inside customermaster so that I will have
something like 1,8,14,25,99 in the mailinglists field for the email
address jo*****@nowhere.com

How can I accomplish this for MS SQL database?

*** Sent via Developersdex http://www.developersdex.com ***

[Ray Costanzo [MVP]]

Why do you want to do that? That is not a good database design by any
means, in my opinion. Actually, what I think you should do is keep your
emailmaster table, but drop the email column and replace it with a
customerid column that holds the ID column from the customermaster table.

Ray at work

"Joey Martin" <jo**@infosmiths.net> wrote in message
news:uQ**************@tk2msftngp13.phx.gbl...

I have two tables:
customermaster (id,name,email,mailinglists)
example: 1,john doe, jo*****@nowhere.com, NULL
emailmaster (email,listid)
example:
jo*****@nowhere.com 1
jo*****@nowhere.com 8
I want to get rid of the emailmaster table and merge the listids, into
the mailinglists field inside customermaster so that I will have
something like 1,8,14,25,99 in the mailinglists field for the email
address jo*****@nowhere.com

How can I accomplish this for MS SQL database?

*** Sent via Developersdex http://www.developersdex.com ***

[Bob Barrows [MVP]]

Joey Martin wrote:

I have two tables:
customermaster (id,name,email,mailinglists)
example: 1,john doe, jo*****@nowhere.com, NULL
emailmaster (email,listid)
example:
jo*****@nowhere.com 1
jo*****@nowhere.com 8

?
This does not seem to be an "emailmaster". It seems to be more of a "bridge"
table to resolve a many-to-many link between customermaster and perhaps a
table called listmaster...
I'm wondering why email was used instead of the id from the customermaster
....

What are the datatypes? is listid numeric or character?

I want to get rid of the emailmaster table and merge the listids, into
the mailinglists field inside customermaster so that I will have
something like 1,8,14,25,99 in the mailinglists field for the email
address jo*****@nowhere.com

This is a HORRIBLE idea. By storing multiple pieces of data in a single
column, you are reducing the ability of the databse to maintain the
integrity of the data, as well as making it much more difficult to write
some queries to retrieve data. Maintaining this data will be a nightmare as
well.

Have you truly thought this through? Run it by someone who knows something
about database design? You really should consult somebody ...

How can I accomplish this for MS SQL database?

What version? If 2000, you can create a UDF (I've taken a guess as to the
datatypes and sizes required):

CREATE FUNCTION dbo.ConcatRows (
@email varchar(80))
RETURNS varchar(500)
AS
BEGIN
DECLARE @val varchar(500)
SET @val=''
SELECT @val= @val +
CASE @val WHEN '' THEN '' ELSE ',' END
+ CAST(listid as varchar(10))
FROM emailmaster
WHERE email = @email

RETURN @val
END

Then, you can use the UDF in an update statment, like this:

UPDATE customermaster
SET mailinglists = dbo.ConcatRows(email)
If SQL 7, you are going to need to do some cursor work, either in T-SQL, or
using an ADO recordset from ASP.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.