Solved

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)

Posted on 2009-07-15
6
277 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:BostonMA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24860116
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
)
go
 
--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);
DECLARE loop_val CURSOR FOR
SELECT DISTINCT cast(id AS varchar(20))
FROM SchemaID;
 
OPEN loop_val;
 
FETCH NEXT FROM loop_val
INTO @id_in;
 
WHILE @@FETCH_STATUS = 0
BEGIN
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;
FETCH NEXT FROM loop_val
INTO @id_in;
END
 
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

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24860125
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...
0
 
LVL 4

Author Comment

by:BostonMA
ID: 24860593
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?  

0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24861415
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.
0
 
LVL 4

Author Closing Comment

by:BostonMA
ID: 31603768
Thanks.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24865578
Welcome and glad to help you out..
0

Featured Post

What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question