In SQL Server 2000, how do I write sql to display grouped data in one row (Similar to how the Stuff function works in 05)

I have a sql statement which groups on department and returns:

Accounting    Ed
Accounting    Mike
Accounting    Peter
Sales    Janet
Sales    Susan

My question is, is it possible in sql (sql server 2000) to return the following:

Accounting    Ed,Mike,Peter
Sales    Janet,Susan

It doesnt have to be seperated by a comma, i just need to know what the technique is to return all the names on the same line as the department.

Here is what the SQL is:

Select Department, Name
From Records
group by Department, Name
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes.. Its possible..
Just follow this example which I did before long time to get it done
--Sample table schema
Create table SchemaID
[ID] smallint,
SchemaID int NOT NULL
--Dummy insert statements
Insert into SchemaID values (1,12)
Insert into SchemaID values (1,13)
Insert into SchemaID values (1,14)
Insert into SchemaID values (2,15)
Insert into SchemaID values (2,16)
Insert into SchemaID values (2,17)
Insert into SchemaID values (2,18)
CREATE TABLE #temp_result ( a varchar(20), b varchar(1000));
Declare @id_con varchar(100);
DECLARE @id_in varchar(100);
SELECT DISTINCT cast(id AS varchar(20))
FROM SchemaID;
OPEN loop_val;
INTO @id_in;
Select @id_con=Coalesce(@id_con + ', ', '') + Cast(SchemaID AS varchar(5)) From SchemaID Where [ID] = @id_in
INSERT INTO #temp_result VALUES (@id_in, @id_con);
SET @id_con = NULL;
INTO @id_in;
CLOSE loop_val;
DEALLOCATE loop_val;
SELECT * FROM #temp_result
-- Drop table once you fetch your Results
DROP TABLE #temp_result

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
ID in my example is your Department and Name combination.
Just try yourself once with the code sample and if you face any errors, ready to help you...
BostonMAAuthor Commented:
I get the idea of your solution, and i could probably make it work given enough time, however at the present I dont have security access to create a table. Do you know of any other ways which dont involve creating a temp table?  

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Then I would suggest you to go for table variable which is not available in 2000.
Hence no other approach strikes in my mind for 2000 other than temp table.
BostonMAAuthor Commented:
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome and glad to help you out..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.