asked on

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

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Raja Jegan R

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...

BostonMA

ASKER

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?

Raja Jegan R

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.

BostonMA

ASKER

Thanks.

Raja Jegan R

Welcome and glad to help you out..