Query Help Needed

I would like to know if there is a way in SQL to select from rows of similar data and to limit the result into one row and concatenating the results from many columns into one column. ie show Jones as belonging to more than one department.

Emp Table

Jones     10 Main St     Accounting
Jones      10 Main St     Sales
Jones     10 Main St     IT
Smith     12 King St     Marketing

Query returning:

Jones      10 Main St     Accounting/Sales/IT
Smith     12 King St     Marketing



--------------------------------------------

Also how would I only return rows from a table that have non-matching data in a series of columns.(is there an easy way to do a comparison between many columns? ie In this case, return only people who have changed departments.

Emp Table

Name 1996 1997 1998 1999

Hart         Acct   Acct     Acct     Acct
Smith     Acct    Mktg     Sales     IT
Jones     HR     HR         Mktg     Mktg
Johnson HR     HR         HR       HR

Query returning:

Smith     Acct    Mktg     Sales     IT
Jones     HR     HR         Mktg     Mktg

Any help would be much appreciated.

Thanks,

Greg Received on Wed Sep 05 2001 - 13:25:27 CEST