USE Northwind
GO

CREATE TABLE myTable99 (
	  Department varchar(50)
	, Employee varchar(50)
)
GO

INSERT INTO myTable99 (Department, Employee)
SELECT 'Administration', 'Johnson' UNION ALL
SELECT 'Administration', 'Smith' UNION ALL
SELECT 'Administration', 'Houston'  UNION ALL
SELECT 'Technolgy Services', 'Kaiser'  UNION ALL
SELECT 'Technolgy Services', 'Lopez'  UNION ALL
SELECT 'Technolgy Services', 'Defeis'
GO

DECLARE @x varchar(8000), @y varchar(50)

SELECT @y = l.Department, @x = ISNULL(@x,'') + Employee + ','
FROM myTable99 l 
INNER JOIN (SELECT DISTINCT Department FROM myTable99) r
ON l.Department = r.Department
SELECT @y, @x  
GO

DROP TABLE myTable99
GO