Access/SQL: Condensing multiple rows into one row

[shaneholmes]

I have a table with employees and their email addresses. Unfortunately for my company, when the info was entered, nobody bothered to check if the same person has multiple email addresses.

Now, I have 3 rows with John Doe's email addresses, 2 rows for Sam Johnson, 2 rows for myself, etc..

Employee_id Name Email
23 John Doe [email protected]
23 John Doe [email protected]
23 John Doe [email protected]
1 Shane Holmes [email protected]
1 Shane Holmes [email protected]
34 Sam Johnson [email protected]
34 Sam Johnson [email protected]

I want to condense the multiple rows so I have one row per employee but with all of the email addresses:

Employee_id Name Email
23 John Doe [email protected], [email protected], [email protected]
1 Shane Holmes [email protected], [email protected]
34 Sam Johnson [email protected], [email protected]

My friend said we should run a query and do a query of a query. Problem is, I'm still getting multiple rows per employee. Can anybody suggest a fix? Maybe I'm joining the queries wrong or maybe I don't need a query at all?

[rmiao]

Then the table is not in first normal form.

[shaneholmes]

How can I resolve this problem? According to another friend of mine, the only way to concatenate would be to write code.

I dont know enough about databases and even less about code to get the job done.

[skhanal]

You have to write a procedure with cursor to loop through all email addresses for the same individual and update one row and delete rest.

You can't do it with one simple T-SQL Statement