sql server msde: Hello,

I have a table simular to the following:

ID = int (Key)
Name= VarChar
Department = nChar

Example Data
ID Name Department
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S

I am looking for a query that will Return

Name All Departments
Chuck ATS
Mark AS

Without duplicate Names.

Thanks,

Chuck

hi Chuck,
[quoted text, click to view]

you have a problem.... your primary key is [Id] but you have to "group" on
[Name] column... so I do hope the [Name] attribute is somewhat "unique" and
you do not have Chuck <> Chuck... that's to say Chuck always is the same
person...
if this is the case, you can aggregate departments in a user defined
function as following...
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.TestTable (
Id int NOT NULL IDENTITY PRIMARY KEY,
Name varchar(10) NOT NULL,
Department nchar(1) NOT NULL
);
INSERT INTO dbo.TestTable VALUES ( 'Chuck', 'A' );
INSERT INTO dbo.TestTable VALUES ( 'Mark', 'A' );
INSERT INTO dbo.TestTable VALUES ( 'Chuck', 'T' );
INSERT INTO dbo.TestTable VALUES ( 'Chuck', 'S' );
INSERT INTO dbo.TestTable VALUES ( 'Mark', 'S' );
GO
CREATE FUNCTION dbo.GetDepartments(
@Name varchar(10)
)
RETURNS nvarchar(50)
AS BEGIN
DECLARE @Departements nvarchar(50);
SET @Departements = '';

SELECT @Departements = @Departements + [Department]
FROM dbo.TestTable
WHERE [Name] = @Name;

RETURN (@Departements);
END;
GO
SELECT * FROM dbo.TestTable;

SELECT [Name], dbo.GetDepartments([Name]) AS [Departments]
FROM dbo.TestTable
GROUP BY [Name];
GO
DROP FUNCTION dbo.GetDepartments;
DROP TABLE dbo.TestTable;
--<---------
Id Name Department
----------- ---------- ----------
1 Chuck A
2 Mark A
3 Chuck T
4 Chuck S
5 Mark S

Name Departments
---------- ------------
Chuck ATS
Mark AS

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
--------- remove DMO to reply